如何从一组名称中的 SQL Server 2000-2008 中的多个表中选择列名称 [英] 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')
在 SQL Server 2000 - 2008 中检索包含上述特定数据库列名的不同表名的最佳方法是什么?
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')
这是这个问题的衍生 我相信.
谢谢
推荐答案
对于 SQL Server 2005 及更高版本
对于较新版本的 SQL Server 的 FWIW 我更喜欢目录视图而不是 INFORMATION_SCHEMA
,原因在这篇博文中列出:
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
另请参阅关于主题 TABLES (Transact-SQL) 的警告 在 MSDN 上:
Also see warnings like this one on the topic TABLES (Transact-SQL) on MSDN:
不要使用 INFORMATION_SCHEMA 视图来确定对象的架构.查找对象架构的唯一可靠方法是查询 sys.objects 目录视图.INFORMATION_SCHEMA 视图可能不完整,因为它们并未针对所有新功能进行更新.
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.
因此,我将使用的查询如下(过滤掉系统对象并在您使用 tempdb 时避免使用 #temp 表):
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;
(COLLATE
子句用于防止在数据库具有不同排序规则的情况下出错.)
(The COLLATE
clauses are there to prevent errors in the case where you have databases with different collations.)
请注意,上述内容对 SQL Server 2000 没有帮助,但我认为您不应该将能够在每个版本上运行相同的查询作为目标.SQL Server 2000 已经有 13 年的历史了,而且已经有好几年没有支持了;当然,您可以证明拥有特殊代码是合理的.在这种情况下,我仍然会选择您对 INFORMATION_SCHEMA
的查询,只需过滤掉系统对象和临时表(同样,仅与您在 tempdb 中的情况相关):
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;
您也可以对 SQL Server 2000 中的每个数据库执行此操作,但是由于您不能使用 NVARCHAR(MAX)
,您将不得不使用游标、一堆变量,或者极不推荐的 sp_msforeachdb.
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.
这篇关于如何从一组名称中的 SQL Server 2000-2008 中的多个表中选择列名称的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!