October 9, 2018 at 10:24 pm
I want to have a go-to query that finds all dependencies of an object, across all databases (without requiring any permanent object like a stored procedure in the host server)
I believe a cursor is one way to go, and below my cursor does loop through all databases and applies the query between BEGIN/END clause, per iteration. Problem is that the database context I am in trumps the database identified per iteration. Rather than finding dependencies in each db, it returns the current context's list of dependencies, appended with a different database name.
What are some ideas for changing database context for each loop?
DECLARE @tbl as varchar(100)
SET @tbl = '%dbo.SHIPMENTS_Claims %'
DECLARE @database as varchar(100)
--get list of databases
DECLARE database_cursor CURSOR LOCAL FOR
SELECT name
FROM Master.dbo.sysdatabases
order by name asc
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
----how to specify database context? unable to use USE & GO here
--SELECT @SqlCommand = 'USE [' + @database + ']' + CHAR(13) + 'GO'
--EXEC (@SqlComman
SELECT
DISTINCT @database + '.dbo.' + a.[name] as ObjectName,
CASE
WHEN a.xtype = 'V' then 'View'
WHEN a.xtype = 'P' then 'SPROC'
ELSE a.xtype END as ObjectType
FROM
--or what to join on so that the database context is changed?
sysobjects a INNER JOIN
syscomments b on
a.id = b.id
WHERE b
.[text] LIKE @tbl
FETCH NEXT FROM database_cursor INTO @database
END
CLOSE database_cursor
DEALLOCATE database_cursor
--Quote me
October 10, 2018 at 3:28 pm
you need to query sysobjects (and other system dbs) in that database.
as in select * from mydatbase.sys.objects.
October 10, 2018 at 3:45 pm
And this will require that you use dynamic SQL to accomplish this task.
October 10, 2018 at 3:56 pm
The easiest and cleanest way to do this would be to create stored proc in the master db, start the name with "sp_", mark it as a system object, and then use that one proc against all your user databases.
But you don't have to do that. You can use code like you have now. Note that a "GO" is not required after a USE <db_name> (not since SQL 2000). So just drop the "GO" from your code.
Also, you can use view sys.sql_expression_dependencies to help you find object references within non-dynamic db code. Of course there's no easy way to find object references within dynamic SQL.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 10, 2018 at 10:43 pm
Steve Jones - SSC Editor - Wednesday, October 10, 2018 3:28 PMyou need to query sysobjects (and other system dbs) in that database.as in select * from mydatbase.sys.objects.
Hi Steve, can you show me how to modify the join?
I tried qualifying the sys.objects view with the database name but query still only returns objects for the database in context.
SELECT
DISTINCT a.[name] as ObjectName,
CASE
WHEN a.type = 'V' then 'View'
WHEN a.type = 'P' then 'SPROC'
ELSE a.type END as ObjectType, *
FROM nameofdatabase.sys.objects a INNER JOIN
syscomments b on
a.object_id = b.id
WHERE
b.[text] LIKE '%shipments%'
--Quote me
October 11, 2018 at 8:02 am
As Lynn mentioned, dynamic SQL is needed.
declare @cmd varchar(max)
-- set cursor
set @cmd = 'select ... from ' + @dbame + '.sys.objects'
-- stuff
exec(@cmd)
October 11, 2018 at 11:54 am
Steve Jones - SSC Editor - Thursday, October 11, 2018 8:02 AMAs Lynn mentioned, dynamic SQL is needed.
declare @cmd varchar(max)
-- set cursor
set @cmd = 'select ... from ' + @dbame + '.sys.objects'
-- stuff
exec(@cmd)
or just use a tool...
I hear there's a really nice one... I believe it's called SQL Dependency tracker.
(Pretty sure Steve had heard of it 🙂 )
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 11, 2018 at 12:10 pm
You can't rely on syscomments for this. The text column is nvarchar(4000), so large procedures can be split into multiple syscomments rows. If your target text is split on a 4000-char boundary, the LIKE won't find it in either row.
Use sys.sql_modules.definition (NVARCHAR(MAX)) instead.
And you'll have to use dynamic SQL, but not necessarily a cursor.
October 14, 2018 at 1:59 pm
I've adopted all suggestions for my query statement except for sys.sql_modules.definition since I need to see how to join to that instead of sys.comments. Right now just trying to get the cursor working. Still the query will not use the context of the database given by the cursor. What is wrong with the below query?
DECLARE @tbl as varchar(100),
@cmd as varchar(max),
@database as varchar(100)
SET @tbl = '%dbo.SHIPMENTS_Claims %'
--get list of databases
DECLARE database_cursor CURSOR LOCAL FOR
SELECT name FROM Master.sys.databases order by name asc
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd = ' use ' + @database +
' SELECT
DISTINCT a.name as ObjectName,
CASE
WHEN a.xtype = ''V'' then ''View''
WHEN a.xtype = ''P'' then ''SPROC''
ELSE a.xtype END as ObjectType
FROM ' + @database + '.sys.objects o INNER JOIN
sysobjects a on o.object_id = a.id INNER JOIN
syscomments b on a.id = b.id
WHERE b.[text] LIKE '+ @tbl
exec(@cmd)
FETCH NEXT FROM database_cursor INTO @database
END
CLOSE database_cursor
DEALLOCATE database_cursor
--Quote me
October 14, 2018 at 2:12 pm
polkadot - Sunday, October 14, 2018 1:59 PMI've adopted all suggestions for my query statement except for sys.sql_modules.definition since I need to see how to join to that instead of sys.comments. Right now just trying to get the cursor working. Still the query will not use the context of the database given by the cursor. What is wrong with the below query?DECLARE @tbl as varchar(100),
@cmd as varchar(max),
@database as varchar(100)
SET @tbl = '%dbo.SHIPMENTS_Claims %'
--get list of databases
DECLARE database_cursor CURSOR LOCAL FOR
SELECT name FROM Master.sys.databases order by name asc
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd = ' use ' + @database +
' SELECT
DISTINCT a.name as ObjectName,
CASE
WHEN a.xtype = ''V'' then ''View''
WHEN a.xtype = ''P'' then ''SPROC''
ELSE a.xtype END as ObjectType
FROM ' + @database + '.sys.objects o INNER JOIN
sysobjects a on o.object_id = a.id INNER JOIN
syscomments b on a.id = b.id
WHERE b.[text] LIKE '+ @tbl　
exec(@cmd)
FETCH NEXT FROM database_cursor INTO @database
END
CLOSE database_cursor
DEALLOCATE database_cursor
all tables need to refer to the same database
FROM ' + @database + '.sys.objects o INNER JOIN
' + @database + '.sys.sysobjects a on o.object_id = a.id INNER JOIN
' + @database + '.sys.syscomments b on a.id = b.id
October 15, 2018 at 12:00 am
Thanks frederico_fonseca. I made the correction.
When running I am getting error Msg 102, Level 15, State 1, Line 10 Incorrect syntax near 'dbo'. Can you tell me why I am getting dbo error when I don't even use the dbo schema?
DECLARE @tbl as varchar(100),
@cmd as varchar(max),
@database as varchar(100)
SET @tbl = '%dbo.SHIPMENTS_Claims %'
--get list of databases
DECLARE database_cursor CURSOR LOCAL FOR
SELECT name FROM Master.sys.databases order by name asc
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd = ' use ' + @database + ' SELECT
DISTINCT a.name as ObjectName,
CASE
WHEN a.xtype = ''V'' then ''View''
WHEN a.xtype = ''P'' then ''SPROC''
ELSE a.xtype END as ObjectType
FROM ' + @database + '.sys.objects o INNER JOIN
' + @database + '.sysobjects a on o.object_id = a.id INNER JOIN
' + @database + '.syscomments b on a.id = b.id
WHERE b.[text] LIKE '+ @tbl
exec(@cmd)
FETCH NEXT FROM database_cursor INTO @database
END
CLOSE database_cursor
DEALLOCATE database_cursor
--Quote me
October 15, 2018 at 1:35 am
You're using it in @tbl, but it's not escaped when you combine strings. Try printing the sql before running it.
October 15, 2018 at 9:28 am
andycadley - Monday, October 15, 2018 1:35 AMYou're using it in @tbl, but it's not escaped when you combine strings. Try printing the sql before running it.
Always do this. USe a SELECT @cmd rather than exec(@Cmd) and verify the code works as you expect.
October 16, 2018 at 12:54 am
thanks for the help, guys. Cursor is working and I've incorporated sql_modules. Final result below.
Is there a better way to do this that doesn't involve creating an object on the client's server or 3rd party software? This cursor is VERY slow.
DECLARE @tbl as varchar(100),
@cmd as varchar(max),
@database as varchar(100)
SET @tbl = '%SHIPMENTS_Claims%'
--get list of databases
DECLARE database_cursor CURSOR LOCAL FOR
SELECT name FROM Master.sys.databases order by name asc
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd = ' use ' + @database +
' SELECT DISTINCT
'''+ @database +''' as tablename,
o.name as ObjectName,
CASE o.xtype
WHEN ''FN'' THEN ''FN''
WHEN ''U'' THEN ''Table''
WHEN ''V'' THEN ''View''
WHEN ''P'' THEN ''SPROC'' ELSE o.xtype
END as ObjectType
FROM ' + @database + '.sys.sql_modules c INNER JOIN
' + @database + '.dbo.sysobjects o ON c.object_id = o.id LEFT JOIN
' + @database + '.dbo.sysobjects p ON o.Parent_obj=p.id
WHERE c.definition LIKE '+ '''' + @tbl + ''''
exec(@cmd)
FETCH NEXT FROM database_cursor INTO @database
END
CLOSE database_cursor
DEALLOCATE database_cursor
--Quote me
October 22, 2018 at 9:54 am
A database is a database, and there isn't a way to search across them easily. Each is supposed to be self contained, and making joins across a large number of them isn't something that is architected in there.
I would expect this to be slow, but I'd expect you wouldn't run this often. Is there a case here where you need to do this regularly?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply