How to find particular table is present in which database

Question

How to find particular table is present in which database

rajeshjaiswalraj

SSCrazy

Points: 2574
More actions
January 19, 2014 at 9:32 pm

#281267

I have created a table but i forget in which database. I know the table name but i dont know the database name..
i have to filter my table from all database ... how can i do and find....
Help me .. waiting for your reply

Viewing 6 posts - 1 through 5 (of 5 total)

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

You'd have to search each database on the server. You can look for the table name in the system view INFORMATION_SCHEMA.Tables.

Or, you could go to Red Gate Software and get the free tool SQL Search[/url]. That'll do it for you too.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

rajeshjaiswalraj SSCrazy Points: 2574 More actions · Answer 2

Thank you for reply......

It is possible if we write ant script .... If we can can you tell me how can i start:cool:

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

This script might help you.

DECLARE @bd varchar(128),

@Table_Namevarchar(128) = 'TABLE_NAME_SEARCHED'

CREATE TABLE #Tables(

bdvarchar(128),

table_namevarchar(128))

DECLARE bases CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR

SELECT name

FROM sys.databases

WHERE database_id > 4

AND state = 0

OPEN bases

FETCH NEXT FROM bases INTO @bd

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC(

'INSERT #Tables

SELECT ''' + @bd + ''' bd,

t.name AS table_name

FROM [' + @bd + '].sys.tables t

WHERE t.name = ''' + @Table_Name + '''')

FETCH NEXT FROM bases INTO @bd

END

CLOSE bases

DEALLOCATE bases

SELECT *

FROM #Tables

ORDER BY bd, table_name

DROP TABLE #Tables

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 4

I use RedGate SQL Search and ApexSQL Search(both of them are free and integrate with your SSMS) for searching Database objects. They both work flawless...

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

I would also recommend one of the free search tools but for this type of sql is fun to write so I provide this.

declare @sql nvarchar(max) = ''

declare @TableName sysname = 'Your table Name here'

select @sql = @sql + 'select ''' + name + ''' as DatabaseName from ' + name + '.sys.tables where name = ''' + @TableName + ''' union all '

from sys.databases

order by name

set @sql = STUFF(@SQL, len(@SQL) - 9, 11, '')

exec sp_executesql @sql

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/