Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

How to find a table when you don't know which database it belongs to?

Dec 16 2011 12:00AM by Naomi (pending approval)   

In this very short tip I'd like to share my solution to a yesterday quesiton in MSDN forum - how to find the database the table belongs to. I use my favorite idea of dynamically constructing a query using INFORMATION_SCHEMA.TABLES view.

     DECLARE @table SYSNAME
    ,@SQL NVARCHAR(MAX)

SET @table = 'Items'
SET @SQL = ''

SELECT @SQL = (
    	SELECT ';
        IF EXISTS (SELECT 1 from ' + QUOTENAME(NAME) + '.INFORMATION_SCHEMA.Tables WHERE Table_Name = @table
        AND TABLE_TYPE = ''BASE TABLE'')
         PRINT ''Table ' + @table + ' found in ' + NAME + ''''
    	FROM sys.databases
    	ORDER BY NAME
    	FOR XML PATH('')
    		,type
    	).value('.', 'nvarchar(max)')

EXECUTE sp_executeSQL @SQL
    ,N'@table sysname'
    ,@table

That's all. Try it with the table which you may have in multiple databases.

Read More..   [19 clicks]

Published under: SQL Server Tips ·  ·  ·  · 

  • Previous: 
  • Next: 

Naomi
31 · 6% · 1776
15
 
6
 
10
 
0
Incorrect
 
0
Interesting
 
0
Forgotten



Submit

1  Comments  

  • Also this will work

    DECLARE @tblNameToSearch VARCHAR(100) = 'test'
    DECLARE @t TABLE (SchemaName SYSNAME,DBName SYSNAME, TableName SYSNAME)
    INSERT INTO @t (SchemaName,DBName,TableName)
    EXEC sp_msforeachdb 'SELECT 
        					s.name AS SchemaName
        					,''?'' AS DbName
        					, t.name AS TableName 
        			  FROM [?].sys.tables t
        			  JOIN sys.schemas s 
        			  ON t.schema_id=s.schema_id'
    SELECT * FROM @t
    WHERE TableName LIKE '%' + @tblNameToSearch + '%'
    
    commented on Dec 21 2011 10:59PM
    Niladri Biswas
    7 · 21% · 6710

Your Comment


Sign Up or Login to post a comment.

"How to find a table when you don't know which database it belongs to?" rated 5 out of 5 by 15 readers
How to find a table when you don't know which database it belongs to? , 5.0 out of 5 based on 15 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]