Script to find all Cross database dependencies in SQL

As part of one of the Database migration analysis we wanted to find all cross database dependencies on the Server. I end up writing a script to search all modules. This is the first version and please let me know if you have any suggestions to improve this script!

-- This Script is to find all Cross Database and Linked Server dependency 
-- Script is Created on 2015-12-02 by VIJRED


DECLARE @myQuery nvarchar(max)
DECLARE @mydbanme varchar(100)
DECLARE @mysearchanme varchar(100)
DECLARE @i int
DECLARE @j int 

DECLARE @maxdbid int
DECLARE @maxsearchid int 


IF OBJECT_ID('tempdb..##dependency_Full') IS NOT NULL
    DROP TABLE ##dependency_Full
CREATE TABLE ##dependency_Full(
	[ServerName] [nvarchar](128) NULL,
	[Databasename] [varchar](128) NULL,
	[name] [nvarchar](1024) NULL,
	[type_desc] [nvarchar](60) NULL,
	[create_date] [datetime] NULL,
	[modify_date] [datetime] NULL,
	[dependency] [varchar](128) NULL
)


IF OBJECT_ID('tempdb..##dependency') IS NOT NULL
    DROP TABLE ##dependency
CREATE TABLE ##dependency(
	[ServerName] [nvarchar](128) NULL,
	[databasename] [varchar](128) NULL,
	[dependency] [varchar](128) NULL
)



SET @i = 1 
SET @j = 1

select IDENTITY(Int, 1,1) AS ID, name into ##dbnames from sys.databases order by name

;WITH CTE AS (
SELECT srvname as name FROM sysservers
where srvid <> 0 
UNION 
select name from sys.databases )
select IDENTITY(Int, 1,1) AS ID, * into ##searchnames from CTE  order by name

select @maxdbid = max(id) from ##dbnames 
select @maxsearchid = max(id) from ##searchnames 


WHILE(@i <= @maxdbid)
BEGIN
	select @mydbanme = name from ##dbnames where id = @i 
	SET @j = 1 

	select @mydbanme = name from ##dbnames where id = @i 
	select @mysearchanme = name from ##searchnames where id = @j

	if (@mydbanme = @mysearchanme)
	begin
		SET @j = 2
		select @mysearchanme = name from ##searchnames where id = @j
	end


	set @myQuery = 'SELECT @@servername as ServerName, ''' + @mydbanme +''' as Databasename, schema_name(schema_id) + ''.''+ so.name as name, so.type_desc, create_date,   modify_date , ''' +@mysearchanme+ ''' as dependency 
	FROM '+@mydbanme+'.sys.sql_modules sm 
	join '+@mydbanme+'.sys.objects so on so.Object_id = sm.Object_id 
	WHERE definition LIKE ''%' + @mysearchanme + '.%''
	'

	SET @j = @j+1
	WHILE(@j <= @maxsearchid)
	BEGIN
		select @mysearchanme = name from ##searchnames where id = @j
		if(@mydbanme <> @mysearchanme)
		BEGIN

			set @myQuery = @myQuery + 'UNION ALL 
			SELECT @@servername as ServerName, ''' + @mydbanme +''' as Databasename, schema_name(schema_id) + ''.''+ so.name as name, so.type_desc, create_date,   modify_date , ''' +@mysearchanme+ ''' as dependency 
			FROM '+@mydbanme+'.sys.sql_modules sm 
			join '+@mydbanme+'.sys.objects so on so.Object_id = sm.Object_id 
			WHERE definition LIKE ''%' + @mysearchanme + '.%''
			'

		END
	SET @j = @j+1
	END

	SET @myQuery = 'IF OBJECT_ID(''tempdb..#tempd'') IS NOT NULL
	    DROP TABLE #tempd
	;WITH CTE AS (
	' + @myQuery + '
				)

	select ServerName, Databasename, name, type_desc , create_date , modify_date , dependency into #tempd from CTE

	insert into ##dependency(ServerName,databasename,dependency)
	select distinct ServerName, databasename, dependency from #tempd 

	insert into ##dependency_full(ServerName, Databasename, name, type_desc , create_date , modify_date , dependency)
	select ServerName, Databasename, name, type_desc , create_date , modify_date , dependency from #tempd'

	--select @myQuery
	exec(@myQuery)

SET @i = @i+1 
END

select * from ##dependency
select * from ##dependency_full

drop table ##dependency_full
drop table ##dependency
drop table ##dbnames
drop table ##searchnames

--
Advertisements
This entry was posted in Productivity, SQL, SQL Query, SQL Server, Uncategorized and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s