<xmp>UNKNOWN
--**************************************
-- Name: Sql Depends
-- Description:Check all procedures, views, triggers for tables used. sysdepends does not work! Builds a list of tables, and scans the syscomments table for occurrences of each table name.
-- By: David Hay
--
--
-- Inputs:Database name
--
-- Returns:servername, database name, table name, and used by (proc, view, trigger)
--
--Assumes:None
--
--Side Effects:na
--This code is copyrighted and has limited warranties.
--Please see http://www.Planet-Source-Code.com/xq/ASP/txtCodeId.1256/lngWId.5/qx/vb/scripts/ShowCode.htm
--for details.
--**************************************

declare @DBName VARCHAR(80)
declare @cDBName VARCHAR(80)
declare @cSchema VARCHAR(80)
declare @cTable VARCHAR(80)
declare @strSql nvarchar(4000)
set @dbname='mydbname'
CREATE TABLE #TempTableList
			 (DatabaseName VARCHAR(80),
			 SchemaName VARCHAR(80),
			 TableName VARCHAR(80),
		 	Process INT DEFAULT 0		 
			 )
CREATE TABLE #TempDependsList
			 (TblName VARCHAR(80),
			 UsedBy VARCHAR(80),
			 xType VARCHAR(3),
			 )
SET @StrSQL = N'USE ' + @DbName + ';SELECT ' +''''+ @DbName +'''' +'AS DatabaseName, ' + 
'QUOTENAME(user_name(uid)) As SchemaName
--     , QUOTENAME([name]) As TableName FROM ' 
--     + 
QuoteName(@DbName) +'.'+'dbo.sysobjects where type = ''U'' AND [name] NOT LIKE ''dt%'' 
order by QUOTENAME([name])'
	--print @strsql
	INSERT INTO #TempTableList 
		(DatabaseName,
		SchemaName,
		TableName)
	EXEC sp_executesql @StrSQL
update #TempTableList set schemaname=replace(schemaname,'[','')
update #TempTableList set schemaname=replace(schemaname,']','')
update #TempTableList set tablename=replace(tablename,'[','')
update #TempTableList set tablename=replace(tablename,']','')
--select * from #TempTableList
DECLARE TableCursor CURSOR FOR
		 SELECT DatabaseName, SchemaName, TableName 
		 FROM #TempTableList
		 WHERE Process=0
		 ORDER BY TableName ASC
OPEN TableCursor
	FETCH NEXT FROM TableCursor INTO @cdbName,@cschema,@cTable
	WHILE @@FETCH_STATUS = 0
	begin
		--print @ctable
		set @strsql = 'insert into #TempDependsList select distinct ''' + @ctable + ''' as TblName, 
b.[name] as UsedBy, xtype from ' + @cdbname + '..syscomments a inner join ' + @cdbname + 
'..sysobjects b on a.id=b.id where [text
--     ] like ''%' + @ctable + '%'''
--		print @strSql
		exec sp_executesql @strsql
		FETCH NEXT FROM TableCursor INTO @cdbName,@cschema,@cTable
	END
CLOSE TableCursor
DEALLOCATE TableCursor
insert into #TempDependsList ( TblName, UsedBy, Xtype)
select TableName, 'Not Used' as usedby,'' as xType from #TempTableList 
where tablename not in (select distinct tblname from #TempDependsList)
select distinct @@servername as Server, TblName, UsedBy, Xtype from #TempDependsList order by tblName
drop table #TempTableList
drop table #TempDependsList
		</xmp>