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

Question

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.

Answer 1

the table you want is sys.objects

SELECT * 
FROM sys.objects

Answer 2

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)

Answer 3

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%'

Answer 4

For Sql Server 2005

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