I execute a select to get the structure of a table. I want to get info about the columns like its name or if it's null or if it's primary key.. I do something like this
我执行一个select来获取表的结构。我想得到关于列的信息,如它的名称或它是否为null或它是否是主键..我做这样的事情
....sys.columns c...
c.precision,
c.scale,
c.is_nullable as isnullable,
c.default_object_id as columndefault,
c.is_computed as iscomputed,
but for default value i get the id..something like 454545454 but i want to get the value "xxxx". What is the table to search or what is the function to convert that id to the value. Thanks
但是对于默认值,我得到id ...像454545454这样的东西,但我想得到值“xxxx”。要搜索的表是什么或将该id转换为值的函数是什么。谢谢
4 个解决方案
#1
10
You can do this (done a SELECT * just so you can see all the info available):
你可以这样做(完成一个SELECT *,这样你就可以看到所有可用的信息):
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE....
This includes a "COLUMN_DEFAULT" column in the resultset.
这包括结果集中的“COLUMN_DEFAULT”列。
#2
7
Use
使用
Select * From INFORMATION_SCHEMA.COLUMNS
there is a column called COLUMN_DEFAULT
有一个名为COLUMN_DEFAULT的列
#3
1
The property you want is called "cdefault".
您想要的属性称为“cdefault”。
http://sql-server-performance.com/Community/forums/p/20588/114944.aspx
http://sql-server-performance.com/Community/forums/p/20588/114944.aspx
#4
1
'bills' is an example table
'账单'是一个示例表
select
COLUMN_DEFAULT --default
,IS_NULLABLE -- is nullable
,NUMERIC_PRECISION --number of digits (binary or decimal depending on radix)
,NUMERIC_PRECISION_RADIX --decimal places
,NUMERIC_SCALE --number of digits to right of decimal point
,COLUMNPROPERTY(OBJECT_ID('bills'),COLUMN_NAME,'Iscomputed') AS ISCOMPUTED --is computed
from INFORMATION_SCHEMA.columns where TABLE_name='bills'
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME='bills' and CONSTRAINT_TYPE='PRIMARY KEY'
#1
10
You can do this (done a SELECT * just so you can see all the info available):
你可以这样做(完成一个SELECT *,这样你就可以看到所有可用的信息):
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE....
This includes a "COLUMN_DEFAULT" column in the resultset.
这包括结果集中的“COLUMN_DEFAULT”列。
#2
7
Use
使用
Select * From INFORMATION_SCHEMA.COLUMNS
there is a column called COLUMN_DEFAULT
有一个名为COLUMN_DEFAULT的列
#3
1
The property you want is called "cdefault".
您想要的属性称为“cdefault”。
http://sql-server-performance.com/Community/forums/p/20588/114944.aspx
http://sql-server-performance.com/Community/forums/p/20588/114944.aspx
#4
1
'bills' is an example table
'账单'是一个示例表
select
COLUMN_DEFAULT --default
,IS_NULLABLE -- is nullable
,NUMERIC_PRECISION --number of digits (binary or decimal depending on radix)
,NUMERIC_PRECISION_RADIX --decimal places
,NUMERIC_SCALE --number of digits to right of decimal point
,COLUMNPROPERTY(OBJECT_ID('bills'),COLUMN_NAME,'Iscomputed') AS ISCOMPUTED --is computed
from INFORMATION_SCHEMA.columns where TABLE_name='bills'
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME='bills' and CONSTRAINT_TYPE='PRIMARY KEY'