April 7, 2014 at 2:18 pm
Comments posted to this topic are about the item Script to Generate Table to Stored Procedure Cross References
April 23, 2014 at 4:55 pm
Nifty!
As a rule, I need to know where the columns are being used. Since the primary server has over 180000 objects on it of many different types, I need to know more than procedures. So, here is my take on your query.
WITH TableList_CTE (TableName)
AS
(
SELECT TABLE_NAME + CHAR(32) AS TableName
FROM INFORMATION_SCHEMA.TABLES T
WHERE t.TABLE_TYPE = 'BASE TABLE'
)
SELECT TableName, OBJECT_NAME(OBJECT_ID) AS ProcedureName,
CASE WHEN OBJECTPROPERTY(OBJECT_ID, 'IsReplProc') = 1 THEN 'Replication Stored Procedure'
WHEN OBJECTPROPERTY(OBJECT_ID, 'IsExtendedProc') = 1 THEN 'Extended Stored Procedure'
WHEN OBJECTPROPERTY(OBJECT_ID, 'IsProcedure') = 1 THEN 'Stored Procedure'
WHEN OBJECTPROPERTY(OBJECT_ID, 'IsTrigger') = 1 THEN 'Trigger'
WHEN OBJECTPROPERTY(OBJECT_ID, 'IsTableFunction') = 1 THEN 'Table-Valued Function'
WHEN OBJECTPROPERTY(OBJECT_ID, 'IsScalarFunction') = 1 THEN 'Scalar-Valued Function'
WHEN OBJECTPROPERTY(OBJECT_ID, 'IsInlineFunction') = 1 THEN 'Inline function'
WHEN OBJECTPROPERTY(OBJECT_ID, 'IsView') = 1 THEN 'View'
ELSE 'Unknown' END AS ProcedureType
FROM sys.sql_modules S
JOIN TableList_CTE ON 1 = 1
ORDER BY TableName
April 25, 2014 at 5:25 am
Why is a char(32) being added to the table name? I noticed that this script is missing references, I wonder if this could be why?
I'm also curious, why is there an inner join on 1=1? Any reason for this instead of just using a cross join if that was the intended behavior?
April 25, 2014 at 5:49 am
chr(32) (space) isn't the only terminator... you can have brackets, return chars, semi-colons etc..
I submitted a script not dissimilar to yours, which also considers these characters.
http://www.sqlservercentral.com/scripts/SQL+Server+7/65162/
Regards,
David.
April 25, 2014 at 10:15 am
Nice Script. Thanks. Fills the missing link.
Added the DISTINCT keyword to the last part as it was giving me duplicates. Otherwise works great.
Amended dcript below:
WITH TableList_CTE (TableName)
AS
(
SELECT TABLE_NAME + CHAR(32) as TableName
FROM INFORMATION_SCHEMA.TABLES T
WHERE t.TABLE_TYPE='BASE TABLE'
)
SELECT DISTINCT TableName,OBJECT_NAME(object_id) as StoredProcedure
FROM sys.sql_modules S
Join TableList_CTE on 1=1
WHERE objectproperty(object_id,'IsProcedure') = 1
AND CHARINDEX(TableName,Definition,0)<>0
Order by TableName
April 25, 2014 at 10:39 am
Reply to SSC-Enthusiastic:
Nice addition.
Missing a WHERE clause in the inner query:
WHERE CHARINDEX(TableName,Definition,0)<>0
AND a DISTINCT clause.
Amended query:
WITH TableList_CTE (TableName)
AS
(
SELECT TABLE_NAME + CHAR(32) AS TableName
FROM INFORMATION_SCHEMA.TABLES T
WHERE t.TABLE_TYPE = 'BASE TABLE'
)
SELECT DISTINCT TableName, OBJECT_NAME(OBJECT_ID) AS ProcedureName,
CASE WHEN OBJECTPROPERTY(OBJECT_ID, 'IsReplProc') = 1 THEN 'Replication Stored Procedure'
WHEN OBJECTPROPERTY(OBJECT_ID, 'IsExtendedProc') = 1 THEN 'Extended Stored Procedure'
WHEN OBJECTPROPERTY(OBJECT_ID, 'IsProcedure') = 1 THEN 'Stored Procedure'
WHEN OBJECTPROPERTY(OBJECT_ID, 'IsTrigger') = 1 THEN 'Trigger'
WHEN OBJECTPROPERTY(OBJECT_ID, 'IsTableFunction') = 1 THEN 'Table-Valued Function'
WHEN OBJECTPROPERTY(OBJECT_ID, 'IsScalarFunction') = 1 THEN 'Scalar-Valued Function'
WHEN OBJECTPROPERTY(OBJECT_ID, 'IsInlineFunction') = 1 THEN 'Inline function'
WHEN OBJECTPROPERTY(OBJECT_ID, 'IsView') = 1 THEN 'View'
ELSE 'Unknown' END AS ProcedureType
FROM sys.sql_modules S
JOIN TableList_CTE ON 1 = 1
WHERE CHARINDEX(TableName,Definition,0)<>0
ORDER BY TableName
April 25, 2014 at 11:02 am
You could probably improve performance a little by using CHARINDEX(TableName,Definition,0)>0 instead of CHARINDEX(TableName,Definition,0)<>0. CharIndex would never be negative, right?, so > would be more efficient than <>. If you look at the execution plans, using <> causes a hash match step that is not needed using just > (at least on my machine running SQL 2008).
June 24, 2015 at 1:30 am
I happen to have tables with the same name in different schemas (mainly staging.<tablename> and dw.<tablename>), so I tweaked the query a little to display (and use) full table names.
WITH TableList_CTE (TableName)
AS (
SELECT
TABLE_SCHEMA + '.' + TABLE_NAME + CHAR(32) as TableName
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
)
SELECT
TableName,
SCHEMA_NAME(O.schema_id) + '.' + OBJECT_NAME(S.object_id) as StoredProcedure
FROM sys.sql_modules S
INNER JOIN sys.objects O ON O.object_id = S.object_id
INNER JOIN TableList_CTE ON 1 = 1
WHERE OBJECTPROPERTY(S.object_id, 'IsProcedure') = 1
AND CHARINDEX(TableName, Definition, 0) > 0
ORDER BY TableName;
June 24, 2015 at 5:37 am
There are 2 DMVs called Reference and Referencing Objects (something like that) that are way powerful, I wrote script that loops through procs and shows what objects (tables, views, fn, other procs) they touch. Very useful if you need to learn a db fast.
June 24, 2015 at 6:53 am
Here is a link that shows the use of sys.sql_expression_dependencies to list objects referencing a table.
June 24, 2015 at 2:23 pm
Alberto Turelli (6/24/2015)
I happen to have tables with the same name in different schemas (mainly staging.<tablename> and dw.<tablename>), so I tweaked the query a little to display (and use) full table names.
WITH TableList_CTE (TableName)
AS (
SELECT
TABLE_SCHEMA + '.' + TABLE_NAME + CHAR(32) as TableName
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
)
SELECT
TableName,
SCHEMA_NAME(O.schema_id) + '.' + OBJECT_NAME(S.object_id) as StoredProcedure
FROM sys.sql_modules S
INNER JOIN sys.objects O ON O.object_id = S.object_id
INNER JOIN TableList_CTE ON 1 = 1
WHERE OBJECTPROPERTY(S.object_id, 'IsProcedure') = 1
AND CHARINDEX(TableName, Definition, 0) > 0
ORDER BY TableName;
Alberto:
Your script is not catching tables not being referenced by fully qualified name, but just the table name.
Amended:
WITH TableList_CTE (TableName)
AS (
SELECT
TABLE_SCHEMA + '.' + TABLE_NAME + CHAR(32) as TableName
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
)
SELECT
TableName,
SCHEMA_NAME(O.schema_id) + '.' + OBJECT_NAME(s.object_id) as StoredProcedure
FROM sys.sql_modules S
INNER JOIN sys.objects O ON s.object_id = o.object_id
INNER JOIN TableList_CTE ON 1 = 1
WHERE OBJECTPROPERTY(S.object_id, 'IsProcedure') = 1
AND CHARINDEX(RIGHT(TableName, LEN(TableName)-LEN(SCHEMA_NAME(O.schema_id))), Definition, 0) <> 0
ORDER BY TableName;
June 25, 2015 at 2:04 am
Thanks for the tip, Aleksey.
As suggested above, I took a look at sys.dm_sql_referencing_entities and I got to this:
SELECT
SCHEMA_NAME(T.schema_id) + '.' + T.name AS full_table_name,
RE.referencing_schema_name + '.' + RE.referencing_entity_name AS full_proc_name
FROM sys.objects T
CROSS APPLY sys.dm_sql_referencing_entities (SCHEMA_NAME(T.schema_id) + '.' + T.name, 'OBJECT') RE
INNER JOIN sys.objects P ON RE.referencing_id = P.object_id AND P.type = 'P'
WHERE T.type = 'U'
ORDER BY full_table_name,
full_proc_name;
This approach, though, doesn't show some simple SPs I have that simply add constraints to a table (via some ALTER TABLE commands).
June 25, 2015 at 7:19 am
Thanks for the script and the extra comments.
June 25, 2015 at 9:12 am
Excellent scripts. In my testing I found a couple of issues though. When I used the code that uses sys.sql_modules (Alesksey's reply to Alberto) I got some false hits. This happened when the comments (or commented out code) in a stored procedure contained the name of an existing table. This also happened if the name of an existing table formed PART of a table's name in the stored procedure. For example; I have a table named "processes" in a database. The code returned 3 procedures that referenced that table. But in reality the procedures referenced 'sysprocesses'.
On the up side though that same code caught references to tables in procedures that used dynamic SQL. These were missed in the code (for obvious reasons) in the code from Alberto in his reply to Aleksey.
Thanks for all the code. I find both examples to be extremely useful.
Lee
June 25, 2015 at 10:59 am
Lots of theses issues seem text related, using the names of objects for matching -- can we use numeric ids? IT must be searching the text of the procs, thus the false matches, so comments, even quoted text may throw it. I will mess with it but just wondering. Also I am having some issues with schemas, the DMV will show objects form some schemas not others, probably based on what schema I am running the DMV from?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply