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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s