CODEWITHSUNDEEP
sql-server
Ray
Ray

Reputation: 12441

What is the sql to query SqlServer system databases to find an DB object?

I have a large db with many tables and sprocs, and I want to find and see, for example, if there is a table with a name that has "setting" as part of it. I'm not very familiar with SqlServer's System Databases like master, msdb etc., I know there is a way to query one of those dbs to get what I need back, does someone know how to do it?

Thank you, Ray.

Upvotes: 2

Views: 563

Answers (4)

Rohan West
Rohan West

Reputation: 9298

For Sql Server 2005

SELECT * FROM sys.objects where type in ('U') and name like '%setting%'

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415881

SQL Server also supports the standard information schema views. Probably better to use them, since this query should also work across different database engines if you ever need to do a migration:

SELECT * FROM INFORMATION_SCHEMA.tables where table_name LIKE '%Settings%'

Upvotes: 3

ahockley
ahockley

Reputation: 3732

The table with the info you seek is called sysobjects. Here's a query for what you describe:

SELECT * FROM sysobjects WHERE xtype = 'U' AND NAME LIKE '%setting%'

(U is the type for user tables)

Upvotes: 0

Steven A. Lowe
Steven A. Lowe

Reputation: 61233

the table you want is sys.objects

SELECT * 
FROM sys.objects

Upvotes: 1

Related Questions