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
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.)
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.