Find occurrence of object in a database using sql script





Following is very useful script which will give you occurrence of object or will tell you where you have used the object like field name is being used in stored procedures, functions and views.

SELECT
so.name,
so.TYPE,
sc.TEXT
FROM
sysobjects so
INNER JOIN syscomments sc
ON so.id = sc.id
WHERE  sc.TEXT LIKE ‘%[Field Name,Stored procedure name etc]%’

Following script will help to find column from all tables in database.

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ‘%assetid%’
ORDER BY schema_name, table_name;




Tags: , ,

Comments are closed.