Find database where table is located

Question

Find database where table is located

tmakoni

SSC Enthusiast

Points: 161
More actions
April 18, 2014 at 4:59 am

#290411

Comments posted to this topic are about the item Find database where table is located

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

samp.silvercreek Ten Centuries Points: 1170 More actions · Answer 1

I realize that it uses an undocumented Microsoft function, but rather than creating a stored procedure, couldn't you just use:

sp_msforeachdb 'select "?" AS db, * from [?].sys.tables where name like "%SOME_TABLE_NAME%"'

tmakoni SSC Enthusiast Points: 161 More actions · Answer 2

I am not sure if you can control the output of the for each query to limit it to only databases that contain the search table, running the statement as is in the case of a server instance with a relatively large number of databases would result in numerous outputs that may not be necessarily quick to browse for results.

Another reason being that I wanted to return a predefined dataset 'database, schema and table' as a streamlined result.

m mcdonald Hall of Fame Points: 3264 More actions · Answer 3

Try something like

sp_msforeachdb 'select "?" AS db, schema_name(schema_id) as [schema_name], name as table_name from [?].sys.tables where name like "%SOME_TABLE_NAME%"'

Cheers

tmakoni SSC Enthusiast Points: 161 More actions · Answer 4

Good suggestion, I have applied as per below to solve the empty result set issue:

Check this:

Please let me know if there is a way to suppress empty result sets in the sp_msforeachdb proc'.

DECLARE @SEARCHTABLE VARCHAR(250) = 'TEST';

DECLARE @RESULTS TABLE(

table_catalog varchar(500),

table_schema varchar(500),

table_name varchar(500));

DECLARE @SQL VARCHAR(MAX);

SET @SQL = '

sp_msforeachdb

''select "?" AS db, schema_name(schema_id) as [schema_name], name as table_name from [?].sys.tables where name like "%'+@SEARCHTABLE+'%"'';';

INSERT INTO @RESULTS

EXEC (@SQL);

SELECT * FROM @RESULTS;

GO

m mcdonald Hall of Fame Points: 3264 More actions · Answer 5

By inserting into the table variable and querying it you effectively do that...

One more change to only search user created tables (if you happen to search for a word in one)

DECLARE @SEARCHTABLE VARCHAR(250) = 'ERROR';

DECLARE @RESULTS TABLE(

table_catalog varchar(500),

table_schema varchar(500),

table_name varchar(500));

DECLARE @SQL VARCHAR(MAX);

SET @SQL = '

sp_msforeachdb

''select "?" AS db, schema_name(schema_id) as [schema_name], name as table_name from [?].sys.tables where is_ms_shipped = 0 and name like "%'+@SEARCHTABLE+'%"'';';

INSERT INTO @RESULTS

EXEC (@SQL);

SELECT * FROM @RESULTS;

GO

Cheers

max_scalf SSC-Addicted Points: 462 More actions · Answer 6

Hello all,

When i try to run the procedure i get below error...any ideas why? SOrry i am very new to SQL Server.

Msg 139, Level 15, State 1, Procedure proc_FindTable, Line 0

Cannot assign a default value to a local variable.

Msg 137, Level 15, State 2, Procedure proc_FindTable, Line 27

Must declare the scalar variable "@min".

Msg 137, Level 15, State 2, Procedure proc_FindTable, Line 29

Must declare the scalar variable "@min".

Msg 137, Level 15, State 2, Procedure proc_FindTable, Line 32

Must declare the scalar variable "@dbName".

Msg 137, Level 15, State 2, Procedure proc_FindTable, Line 35

Must declare the scalar variable "@sql".

tmakoni SSC Enthusiast Points: 161 More actions · Answer 7

Hi,

The error seems to point to something that has been excluded in the create procedure statement you ran to generate the proc.

Please navigate to the procedure on the database to which you created it, right click and select [Script Stored Procedure as].[CREATE To]. Please paste the code for me to have a look at.

Thanks.