SQL Server：通过查询找出列的默认值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3817885/
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
SQL Server: Find out default value of a column with a query
提问by zad
How can I find out the default value of a column in a table using a SQL query?
如何使用 SQL 查询找出表中列的默认值？
By using this stored procedure:
通过使用这个存储过程：
sp_columns @tablename
I get some information on the columns of a particular table but the default value of the columns is missing, How can I get it?
我获得了有关特定表的列的一些信息，但缺少列的默认值，如何获取？
回答by Martin Smith
You can find the stored definition with the below (remember to adjust the column and table name to find to be ones relevant to your environment!)
您可以使用以下内容找到存储的定义（请记住调整列和表名称以找到与您的环境相关的名称！）
SELECT object_definition(default_object_id) AS definition
FROM sys.columns
WHERE name ='colname'
AND object_id = object_id('dbo.tablename')
回答by OMG Ponies
Use:
用：
SELECT so.name AS table_name,
sc.name AS column_name,
sm.text AS default_value
FROM sys.sysobjects so
JOIN sys.syscolumns sc ON sc.id = so.id
LEFT JOIN sys.syscomments sm ON sm.id = sc.cdefault
WHERE so.xtype = 'U'
AND so.name = @yourtable
ORDER BY so.[name], sc.colid
回答by shA.t
I use INFORMATION_SCHEMAtable like this:
我使用这样的INFORMATION_SCHEMA表：
SELECT
TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = @SchemaName
AND TABLE_NAME = @TableName
AND COLUMN_NAME = @ColumnName;
