SQL 如何从sql server master数据库中查找任何数据库中任何表的列数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1852074/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find columns count of any table in any database from sql server master database?
提问by user366312
If I know the database-name and table-name, how can I find columns-count of the table from sql server master database?
如果我知道数据库名和表名，如何从 sql server master 数据库中找到表的列数？
What is the fastest way to find the columns count of any database-table?
查找任何数据库表的列数的最快方法是什么？
What do you think about the performance of this query?
您如何看待此查询的性能？
select count(*) from SYSCOLUMNS where id=(select id from SYSOBJECTS where name='Categories')
I need to support sql server 2000 and onwards.
我需要支持 sql server 2000 及更高版本。
回答by Tom H
It may vary slightly depending on the version of SQL Server, but this will work for 2005:
根据 SQL Server 的版本，它可能会略有不同，但这适用于 2005：
SELECT
COUNT(*)
FROM
<database name>.sys.columns
WHERE
object_id = OBJECT_ID('<database name>.<owner>.<table name>')
In 2000:
2000年：
SELECT
COUNT(*)
FROM
<database name>.sysobjects o
INNER JOIN <database name>.syscolumns c ON
c.id = o.id
WHERE
o.name = '<table name>'
If you might have multiple tables with the same exact table name under different owners then you'll need to account for that. I forget the column name in sysobjects to look at off the top of my head.
如果您可能在不同的所有者下有多个具有相同表名的表，那么您需要考虑到这一点。我忘记了 sysobjects 中的列名，以便在我的头顶上查看。
UPDATE FOR NEWER VERSIONS OF SQL Server and ANSI compliance:
更新 SQL Server 和 ANSI 合规性的新版本：
SELECT COUNT(*)
FROM
<database name>.INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = '<table schema>' AND
TABLE_NAME = '<table name>'
回答by marc_s
You could (and should) do this - try to avoid using the "sysobjects" view - it's no longer supported and might be removed in SQL Server 2008 R2 or later.
您可以（并且应该）这样做 - 尽量避免使用“sysobjects”视图 - 它不再受支持，可能会在 SQL Server 2008 R2 或更高版本中删除。
Instead, use the "sys" catalog view in the database:
相反，使用数据库中的“sys”目录视图：
SELECT COUNT(*)
FROM yourdatabase.sys.columns
WHERE object_id = OBJECT_ID('yourdatabase.dbo.tablename')
That should do the trick, and it's probably the easiest and fastest way to do it.
这应该可以解决问题，这可能是最简单、最快的方法。
回答by Raj
How about
怎么样
select count(*) from <database name.information_schema.columns where table_name = '<table_name>'
回答by Rahul
you could issue something like this,
你可以发布这样的东西，
select count(*) from information_schema.columns where table_name='yourtablename'
回答by Marius
Or you can view all tables with their columns count
或者您可以查看所有表及其列数
SELECT COUNT(column_name) as "column_count", table_name
FROM INFORMATION_SCHEMA.COLUMNS GROUP BY table_name ORDER BY "column_count" DESC
SELECT COUNT(column_name) as "column_count", table_name
FROM INFORMATION_SCHEMA.COLUMNS GROUP BY table_name ORDER BY "column_count" DESC
回答by SameerDhoot
select Object_name(object_id) as "Object Name", count(*) as "Column Count" from Course_Plannning_Expense.sys.columns
where Object_name(object_id) not like 'sys%'
group by object_id
order by "Column Count" desc
