February 14, 2015 at 7:15 pm
I have a table with the list of all TableNames in the database. I would like to query that table
and find any tables used in any stored procedure in that DB.
Select * from dbo.MyTableList
where Table_Name in
(
Select Name
From sys.procedures
Where OBJECT_DEFINITION(object_id) LIKE '%MY_TABLE_NAME%'
Order by name
)
February 14, 2015 at 9:53 pm
So why can't I pass a varible to this script?
Declare @TableName VARCHAR(30)
SET @TableName = 'MY_HISTORY_TBL'
Select
(Name)
From sys.procedures
Where OBJECT_DEFINITION(object_id) Like @TableName
Order by (Name)
Go
February 14, 2015 at 10:17 pm
I got past the last post but I'm still struggling
DECLARE @Variable1 VARCHAR(MAX), @Variable2 VARCHAR(MAX)
DECLARE CursorName CURSOR FAST_FORWARD
FOR
SELECT TABLE_NAME
FROM dbo.MY_HISTORY_TBL
OPEN CursorName
FETCH NEXT
FROM CursorName
INTO @Variable1
WHILE @@FETCH_STATUS = 0
BEGIN
Select
@TableName = (Name)
From sys.procedures
Where OBJECT_DEFINITION(object_id) Like '%'+@TableName+'%'
Order by (Name)
FETCH NEXT FROM CursorName
INTO @Variable1
END
CLOSE CursorName
DEALLOCATE CursorName
February 14, 2015 at 10:45 pm
Quick suggestion, query the sys.sql_dependencies view
😎
SELECT
OBJECT_NAME(SD.object_id) AS OBJ_NAME
,OBJECT_NAME(SD.referenced_major_id) AS DEPENDENT_NAME
,SD.class_desc AS CLASS_DESCRIPTION
FROM sys.sql_dependencies SD
;
February 15, 2015 at 6:38 pm
Eirikur Eiriksson (2/14/2015)
Quick suggestion, query the sys.sql_dependencies view😎
SELECTOBJECT_NAME(SD.object_id) AS OBJ_NAME
,OBJECT_NAME(SD.referenced_major_id) AS DEPENDENT_NAME
,SD.class_desc AS CLASS_DESCRIPTION
FROM sys.sql_dependencies SD
;
Can you help me develop the query? I'm completely lost
February 16, 2015 at 5:30 am
This will list ALL of the user tables with the locations where they are used. You could substitute your "table of table names" in place of sys.tables...
SELECT
t.name AS table_name,
OBJECT_NAME(m.object_id) AS module_name
FROM
sys.sql_modules m,
sys.tables t
WHERE
CHARINDEX(t.name,m.definition) > 0
February 16, 2015 at 7:28 am
smitty-1088185 (2/14/2015)
I have a table with the list of all TableNames in the database.
Why not query sys.tables instead of maintaining you own list of tables? There really is no need to keep a copy in another location since the system does this for us already.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 17, 2015 at 2:13 pm
smitty-1088185 (2/15/2015)
Eirikur Eiriksson (2/14/2015)
Quick suggestion, query the sys.sql_dependencies view😎
SELECTOBJECT_NAME(SD.object_id) AS OBJ_NAME
,OBJECT_NAME(SD.referenced_major_id) AS DEPENDENT_NAME
,SD.class_desc AS CLASS_DESCRIPTION
FROM sys.sql_dependencies SD
;
Can you help me develop the query? I'm completely lost
SELECT
sed.referenced_entity_name,
o.name AS referencing_object,
o.type_desc AS referencing_type,
sed.is_schema_bound_reference,
sed.is_caller_dependent
FROM sys.sql_expression_dependencies sed
INNER JOIN dbo.MyTableList mtl ON
mtl.table_name = sed.referenced_entity_name
LEFT OUTER JOIN sys.objects o ON
o.object_id = sed.referencing_id
WHERE
(sed.referenced_server_name IS NULL OR sed.referenced_server_name = @@SERVERNAME) AND
(sed.referenced_database_name IS NULL OR sed.referenced_database_name = DB_NAME()) AND
(sed.referenced_schema_name IS NULL OR sed.referenced_schema_name = 'dbo')
ORDER BY
referenced_entity_name,
referencing_object
Edit: Added quotes to link to earlier discussion.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply