5 SQL Tips 4 all


--To search for a particular column in a database


USE

AdventureWorks

;

GO

SELECT

OBJECT_NAME(obj.object_id) as [Name], type_desc as [Type] FROM sys.objects obj inner join sys.columns col ON col.object_id = obj.object_id WHERE col.NAME Like '%Order%' ORDER BY

[Type]

GO

-- To get TableCount() in a DB - number of Tables in a DB

USE

aspnetdb

SELECT

COUNT(*)
from
information_schema.tables
WHERE table_type
=

'base table'




//

Create
Comma Separated
Values from a Select Query Result without using COALESCE function in sql

.



DECLARE

@temp table ([Numbers] varchar(40)

)

INSERT

INTO @temp VALUES('Karthik'

);

INSERT

INTO @temp VALUES('Valpaiyan'

);

INSERT

INTO @temp VALUES('Mangalore Shiva'

);

INSERT

INTO @temp VALUES('PKP'

);

INSERT

INTO @temp VALUES('Idlyvadai'

);



SELECT

DISTINCT STUFF( (SELECT ',' + Numbers from @temp FOR XML PATH('')),1,1,'') as Numbers
FROM

@temp


-- To check a string has number or not.

DECLARE

@x varchar(15), @result varchar(50

)

SET

@x =

'vijaybalaji2001'

IF

PATINDEX('%[0-9]%',@x) >

0

set

@result=

'Number found'

ELSE

set

@result=

'Number not found'

print

@result



-- Leap Year checking using Sql server 2005


if

datepart(dd, cast(cast(datepart(yy, getdate()) as varchar) + '-02-28' as datetime) + 1) =

29

print

'leap year'

else

print

'not a leap year'







--String Concatenation

declare

@x char(10

)

declare

@y char(10

)

declare

@z char(10

)

set

@x =

'Raja '

set

@y =

'Ram '

set

@z =

'Mohan Rai'

print

@x + @y +

@z


No comments: