CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2005sql-server-2000
RetroCoder
RetroCoder

Reputation: 2685

How to select column names from multiple tables in SQL Server 2000-2008 that are in a set of names

If I have a set of names like this: 

('first', 'fname', 'firstname', 'namef', 'namefirst', 'name')

What is the best method in SQL Server 2000 - 2008 to retrieve the distinct table names that contain column names in the above set for a specific database?

And I wanted to exclude system table and temp tables from the list of tables that are displayed.

SELECT so.name
FROM   sysobjects so
       INNER JOIN syscolumns sc
            ON  so.id = sc.id
WHERE  sc.name IN ('first', 'fname', 'firstname', 'namef', 'namefirst', 'name')

This is a derivative of this question I believe.

Thx

Upvotes: 2

Views: 16489

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

For SQL Server 2005 and above

FWIW for newer versions of SQL Server I prefer the catalog views over INFORMATION_SCHEMA for the reasons outlined in this blog post:

The case against INFORMATION_SCHEMA views

Also see warnings like this one on the topic TABLES (Transact-SQL) on MSDN:

Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of an object is to query the sys.objects catalog view. INFORMATION_SCHEMA views could be incomplete since they are not updated for all new features.

So the query I would use would be as follows (filtering out system objects and also avoiding #temp tables in the event you're in tempdb):

SELECT t.name, c.name
  FROM sys.tables AS t
  INNER JOIN sys.columns AS c
  ON t.[object_id] = c.[object_id]
  WHERE c.name IN (N'name', N'firstname', etc.)
  AND t.is_ms_shipped = 0
  AND t.name NOT LIKE '#%';

To repeat this for all databases:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
  UNION ALL SELECT db = N''' + name + ''', 
    t.name COLLATE Latin1_General_CI_AI, 
    c.name COLLATE Latin1_General_CI_AI
  FROM ' + QUOTENAME(name) + '.sys.tables AS t
  INNER JOIN ' + QUOTENAME(name) + 'sys.columns AS c
  ON t.[object_id] = c.[object_id]
  WHERE c.name IN (N''name'', N''firstname'', etc.)
  AND t.is_ms_shipped = 0
  AND t.name NOT LIKE ''#%'''
FROM sys.databases
-- WHERE ... -- probably don't need system databases at least

SELECT @sql = STUFF(@sql, 1, 18, '') 
  -- you may have to adjust  ^^ 18 based on copy/paste, cr/lf, tabs etc.
  + ' ORDER BY by db, s.name, o.name';

EXEC sp_executesql @sql;

(The COLLATE clauses are there to prevent errors in the case where you have databases with different collations.)

For SQL Server 2000

Note that the above doesn't help for SQL Server 2000, but I don't think you should make it a goal to be able to run the same query on every single version. SQL Server 2000 is 13 years old and several years out of support; surely you can justify having special code for it. In which case I would still choose the query you have over INFORMATION_SCHEMA, just filter out system objects and temp tables (again, only relevant in the event you're in tempdb):

SELECT [object] = so.name, [column] = sc.name, 
  [type]  = st.name,   [precision] = st.xprec, 
  [scale] = st.xscale, st.length
FROM sysobjects AS so
INNER JOIN syscolumns AS sc
ON  so.id = sc.id
INNER JOIN systypes AS st
ON sc.xtype = st.xtype
WHERE sc.name IN 
  (N'first', N'fname', N'firstname', N'namef', N'namefirst', N'name')
AND so.name NOT LIKE '#%'
AND OBJECTPROPERTY(so.id, 'IsMsShipped') = 0;

You can do this for each database in SQL Server 2000 too, but since you can't use NVARCHAR(MAX) you will either have to use a cursor, a bunch of variables, or the highly not-recommended sp_msforeachdb.

Upvotes: 4

cjk
cjk

Reputation: 46415

You can use the INFORMATION_SCHEMA schema.

SELECT DISTINCT t.TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES t
  JOIN INFORMATION_SCHEMA.COLUMNS c
  ON t.TABLE_NAME = c.TABLE_NAME
  WHERE c.COLUMN_NAME IN (your list of names)

I don't recall whether this includes temp tables, but you can look at the other columns in TABLES to see what to filter on.

Upvotes: 5

Related Questions