how to seach a table name in a sever? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to seach a table name in a sever?

Author

Topic

jeff06
Posting Yak Master

166 Posts

Posted - 2009-04-14 : 13:31:44

My server has lots of databases and each db has lots of tables.
I want to find if there is a table named table1 in the the sever. Is there a way to do that?
THX.
Jeff

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-14 : 13:46:11

Take a look at the information_schema table.

jeff06
Posting Yak Master

166 Posts

Posted - 2009-04-14 : 14:12:01

Thanks.
information_schema only have info for a specific db. i want to find all table name table1 in entire server. In there a way to do that?
Jeff

andrewz00
Starting Member

15 Posts

Posted - 2009-04-14 : 14:16:11

try this one...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44682

but the best ive come up with is to search each db for the table name...

my 2 fav queries:
--table or column name
select
O.Name,
C.Name
from syscolumns C with (nolock)
INNER JOIN Sysobjects O with (nolock)
ON C.ID = O.ID
Where
o.Type = 'U'
AND
c.xtype in(167,175, 231,239)

ORDER BY
O.Name,
C.Name

--primary keys

SELECT KU.*
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources