Monday, 29 July 2013

Finding Objects on Your SQL Instance

edit the SET @Object = 'YourTableName' with the name of table.
DECLARE @Cursor CURSOR
DECLARE @DatabaseName VARCHAR(100)
DECLARE @Object VARCHAR(100)
DECLARE @Message VARCHAR(100)

--Enter object you are trying to find:
SET @Object = 'YourTableName'

SET @Message = 'Object found in database: '
SET @Cursor = CURSOR FOR
     SELECT
      name
     FROM
      sys.databases
     WHERE
      [state] = 0 --0 = Online

OPEN @Cursor
FETCH NEXT FROM @Cursor INTO
       @DatabaseName

WHILE (@@FETCH_STATUS = 0)
BEGIN

 DECLARE @SQL VARCHAR(MAX)
 SET @SQL = 
 
 '
 IF (SELECT COUNT(0) FROM [' + @DatabaseName + '].sys.objects WHERE name = ''' + @Object + ''') > 0
 BEGIN
 PRINT ''' + @Message + @DatabaseName + '''
 END
 '

 --PRINT @SQL
 EXEC (@SQL)
 FETCH NEXT FROM @Cursor INTO
        @DatabaseName

END

CLOSE @Cursor
DEALLOCATE @Cursor

No comments:

Post a Comment