I want these 5 columns as output when a stored procedure, in any database on the server, references a column in a specific database (let's say the database is AA).
Column_name Table_Name Schema_Name Procedure_Name Database_Name 
2 Answers
Four of the columns are really easy to get - for the current database that you're in:
SELECT TableName = t.Name, SchemaName = s.Name, ColumnName = c.Name, DatabaseName = DB_NAME() FROM sys.tables t INNER JOIN sys.schemas s ON [t].[schema_id] = [s].[schema_id] INNER JOIN sys.columns c ON [t].[object_id] = [c].[object_id] What you cannot get easily is all columns across all tables in all databases. Also: determining in which procedure each column is used is also rather tricky (or next to impossible).
8 Comments
Chow
Thanks for Quick reply Marc. I need info of all the columns in a particular database used in all procedures of all databases. Is it possible? Say if a column 'CC' is used in particular db 'AA' and in particular procedure 'PP', I need the output CC PP AA.
Nithesh Narayanan
@marc_S Can we do something with
sys.sysdepends table?
marc_s
@user2666339: as I said - no, getting that information (which column is used in which procedure) is NOT easy at all. You would almost have to load all the procedure's T-SQL code and parse it.....
marc_s
@Nithesh: that only tells you which tables (or views etc.) a procedure depends on - it doesn't go to the column level....
Nithesh Narayanan
@marc_s i generated a query from your answer which gives comma seperated string of sp names (dependent by the table). kindly have a look and comment pls
|
From marc_s answer i generate a query which gives comma separated Procedure_Name depended by that table
SELECT TableName = t.Name, SchemaName = s.Name, ColumnName = c.Name, DatabaseName = DB_NAME(), STUFF((SELECT ',' + name FROM sys.procedures WHERE object_id in (SELECT distinct id from sys.sysdepends WHERE depid = (SELECT object_id FROM sys.tables WHERE name=t.Name) ) FOR XML PATH('')), 1, 0, '') AS sps FROM sys.tables t INNER JOIN sys.schemas s ON [t].[schema_id] = [s].[schema_id] INNER JOIN sys.columns c ON [t].[object_id] = [c].[object_id] answered Aug 12, 2013 at 14:11
Nithesh Narayanan
11.8k35 gold badges102 silver badges140 bronze badges
2 Comments
Chow
With the above query, I am getting only the list of procedures in the database which used (need to validate though got some info). Is there a way I can get all the info in all SPs of all the databases. Thanks.
Chow
It returned all the columns in a particular table as if all are used even though only few of the columns were used in a stored procedure.
database..table AS tordatabase..[table] AS t1- now you have to parse all of the code for references to anyt.columnort1.columnand hope that they're prefixed (they're not always because people are lazy).