question

jwcoving avatar image
jwcoving asked

How do I find out

I have a SQL2005 Instance (Full SQL). How do I query this instance to find out many tables there are nameed MYTABLE? In this instance, there are dozens of Databases.
sql-server-2005t-sqlsystem-tables
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo avatar image
Leo answered
[USE DATABASENAME] SELECT * FROM sys.OBJECTS WHERE NAME LIKE '%MYTABLE%'
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

don't forget to filter on type='u'
1 Like 1 ·
ThomasRushton avatar image
ThomasRushton answered
For each database? sp_msforeachDB 'select ''?'' AS DBName, * from [?].sys.objects WHERE Name = ''MYTABLE''' Actually, that won't return the count of DBs with that object, but it will return rows where the database does contain the named object...
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

don't forget to filter on type='u'
2 Likes 2 ·
But that'll only provide user tables - what if he's also looking for system tables? ;-)
0 Likes 0 ·
Thinking about it, it's a SQL 2005 instance, so I should have suggested .... from [?].sys.tables WHERE ....
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.