Thursday, August 9, 2012

TSQL SysObjects queries

Ooh..Yesterday I had to work on huge db and fortunately I had the SYSxxxx sql queries.

Following are for your reference too :-)

--To know the table names where ever the given column exists
SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM
syscolumns WHERE name like '%ChargeDescription%' )

-- To know the SP Names from database
select schema_name(schema_id) as [schema], 
       name
from sys.procedures
where name like '%calendar%month%'


-- To find the text in SPs accross the database
SELECT OBJECT_NAME(id) 
    FROM SYSCOMMENTS 
    WHERE [text] LIKE '%FSDFeeSchedulesCharges%' 
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
    GROUP BY OBJECT_NAME(id)


-- Find Stored Procedure Related to Table in Database – Search
in All Stored Procedure
 SELECT DISTINCT o.name, o.xtype
 FROM syscomments c
 INNER JOIN sysobjects o ON c.id=o.id
 WHERE c.TEXT LIKE '%FSDFeeSchedulesCharges%'

No comments: