SQL Query to get Constraint Information

SELECT ISNULL(c.constraint_name, '') ConstraintName
 ,ISNULL(cfk.table_name, '') FK_Table_name
 ,ISNULL(kcu.column_name, '') FK_Column_name
 ,ISNULL(cpk.table_name, '') PK_Table_name
 ,ISNULL(pkt.column_name, '') PK_COlumn_name
FROM information_schema.REFERENTIAL_CONSTRAINTS c
INNER JOIN information_schema.TABLE_CONSTRAINTS cpk ON c.unique_constraint_name = cpk.constraint_name
INNER JOIN information_schema.TABLE_CONSTRAINTS cfk ON c.constraint_name = cfk.constraint_name
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON c.constraint_name = kcu.constraint_name
INNER JOIN (
 SELECT tci.table_name
  ,kcui.column_name
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tci
 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcui ON tci.constraint_name = kcui.constraint_name
 WHERE tci.CONSTRAINT_TYPE = 'PRIMARY KEY'
 ) PKT ON PKT.TABLE_NAME = CPK.TABLE_NAME
GROUP BY c.constraint_name
 ,cfk.table_name
 ,kcu.column_name
 ,cpk.table_name
 ,pkt.column_name
HAVING count(pkt.column_name) = 1
--and cfk.table_name = 'pf_child'
ORDER BY 1
 ,2
 ,3
 ,4

SQL Query to get Foreign Key Details

SELECT f.name AS ForeignKey,

SCHEMA_NAME(f.SCHEMA_ID) SchemaName,

OBJECT_NAME(f.parent_object_id) AS TableName,

COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,

SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,

OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,

COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName

FROM sys.foreign_keys AS f

INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

Search a text from all Stored Procedures

SELECT obj.Name SPName, sc.TEXT SPText

FROM sys.syscomments sc

INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID

WHERE sc.TEXT LIKE '%' + 'YouTextHere' + '%'

AND TYPE = 'P'

SQL UDF to get No. of Days in a Month

CREATE FUNCTION [dbo].[fn_GetNumDaysInMonth] ( @myDateTime DATETIME )

RETURNS INT

AS

BEGIN

DECLARE @rtDate INT

SET @rtDate = CASE WHEN MONTH(@myDateTime)

IN (1, 3, 5, 7, 8, 10, 12) THEN 31

WHEN MONTH(@myDateTime) IN (4, 6, 9, 11) THEN 30

ELSE CASE WHEN (YEAR(@myDateTime) % 4 = 0

AND

YEAR(@myDateTime) % 100 != 0)

OR

(YEAR(@myDateTime) % 400 = 0)

THEN 29

ELSE 28 END

END

RETURN @rtDate

END

SQL Query to display all User Defined Functions

SELECT name AS function_name

,SCHEMA_NAME(schema_id) AS schema_name

,type_desc

FROM sys.objects

WHERE type_desc LIKE '%FUNCTION%'

SQL UDF to get Currency in Comma seperated

CREATE FUNCTION [dbo].[fn_CurrencyInComma]

(

       @Value DECIMAL(18, 2)

)

RETURNS VARCHAR(50)

AS

BEGIN

       DECLARE       @s VARCHAR(50),

              @ptr SMALLINT

       --FOR INDIA SET 2, AND FOR UK SET 3

       DECLARE @IndiaOrUK SMALLINT = 2

       SELECT @s = CAST(@Value AS VARCHAR(50)),

              @ptr = DATALENGTH(@s) - 5

       WHILE @ptr >= 2

              SELECT @s = STUFF(@s, @ptr, 0, ','),

                     @ptr = @ptr - @IndiaOrUK

       RETURN @s

END

Working with xp_cmdshell

xp_cmdshell executes Operating system command through SQL.
Syntax:
EXEC xp_cmdshell {'command_string'} [, no_output]

command_string:
        type here operating system commands.

no_output:
        if no_output is specified, it will not return values. By default it returns 0(Success) or 1(Failure).

How to enable xp_cmdshell:

EXEC sp_configure 'show advanced options',1

GO

RECONFIGURE

GO

EXEC sp_configure 'xp_cmdshell', 1

GO

RECONFIGURE

GO

Example:

EXEC xp_cmdshell 'ver'

EXEC xp_cmdshell 'ipconfig'

EXEC xp_cmdshell 'vol'

EXEC xp_cmdshell 'dir'

Now lets play more with it.

--create a textfile and write some content into it

EXEC xp_cmdshell 'echo hello, this is a text msg >h:\test.txt'

--now read it

EXEC xp_cmdshell 'type h:\test.txt'

--make a newdirectory

EXEC xp_cmdshell 'md h:\to'

--copy this textfile 'test.txt' to another location

EXEC xp_cmdshell 'copy h:\test.txt h:\to\copied.txt'

--now see newlycreated directory and check presense of your copied file

EXEC xp_cmdshell 'dir h:\to'

--now read againfrom copied location for verification

EXEC xp_cmdshell 'type h:\to\copied.txt'

--rename this file

EXEC xp_cmdshell 'ren h:\to\copied.txt renamed.txt'

--delete this file

EXEC xp_cmdshell 'del h:\to\renamed.txt'

--and removedirectory too

EXEC xp_cmdshell 'rd h:\to\'