SQL Tips and Tricks

— TO GET STORED PROCEDURE TEXT/INFORMATION

SP_HELPTEXT  ‘<STORE PROCEDURE NAME>’;

— TO RENAME THE STORED PROCEDURE

SP_RENAME ‘<CURRENT STORED PROCEDURE NAME>’, ‘<NEW STORED PROCEDURE NAME>’;

— TO CHECK TABLE DEPENDENCIES

SELECT DISTINCT SYSOBJ.NAME, SYSOBJ.XTYPE

FROM SYSCOMMENTS SYSCOM

INNER JOIN SYSOBJECTS SYSOBJ ON SYSCOM.ID = SYSOBJ.ID

WHERE SYSCOM.TEXT LIKE ‘%<TABLE NAME>%’;

— TO FIND TABLE NAME CONTAINS COLUMN NAME

SELECT COLUMN_NAME, TABLE_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME LIKE ‘%<COLUMN NAME>%’;

— TO SEARCH STORED PROCEDURE CONTAINING A CPARTICULAR TEXT

SELECT ROUTINE_NAME, ROUTINE_DEFINITION

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_DEFINITION LIKE ‘%<COLUMN NAME>%’

AND ROUTINE_TYPE = ‘procedure’;

— TO GET SEQUENCE NO

SELECT ROW_NUMBER() OVER(PARTITION BY OfficeLocation ORDER BY UserID ASC) AS RecordNo , UserID , UserEmail , OfficeLocation , UserName FROM dbo.UserMaster;

— TO SEARCH STORED PROCEDURE CONTAINING A CPARTICULAR TEXT