我需要在SQL server上编写一个查询,以获得特定表中的列列表,其相关数据类型(长度)以及它们是否不为空。我已经做到了这么多。
但现在我还需要得到,在同一个表中,对一个列- TRUE,如果该列是一个主键。
我该怎么做呢?
我的期望输出是:
Column name | Data type | Length | isnull | Pk
我需要在SQL server上编写一个查询,以获得特定表中的列列表,其相关数据类型(长度)以及它们是否不为空。我已经做到了这么多。
但现在我还需要得到,在同一个表中,对一个列- TRUE,如果该列是一个主键。
我该怎么做呢?
我的期望输出是:
Column name | Data type | Length | isnull | Pk
当前回答
查询:执行sp_description _first_result_set并选择年收入 [DB_NAME]。[DBO]。(员工)”
注意:在某些ide中选择n之前是工作的,或者在某些ide中没有选择n是工作的
其他回答
select
c.name as [column name],
t.name as [type name],
tbl.name as [table name]
from sys.columns c
inner join sys.types t
on c.system_type_id = t.system_type_id
inner join sys.tables tbl
on c.object_id = tbl.object_id
where
c.object_id = OBJECT_ID('YourTableName1')
and
t.name like '%YourSearchDataType%'
union
(select
c.name as [column name],
t.name as [type name],
tbl.name as [table name]
from sys.columns c
inner join sys.types t
on c.system_type_id = t.system_type_id
inner join sys.tables tbl
on c.object_id = tbl.object_id
where
c.object_id = OBJECT_ID('YourTableName2')
and
t.name like '%YourSearchDataType%')
union
(select
c.name as [column name],
t.name as [type name],
tbl.name as [table name]
from sys.columns c
inner join sys.types t
on c.system_type_id = t.system_type_id
inner join sys.tables tbl
on c.object_id = tbl.object_id
where
c.object_id = OBJECT_ID('YourTableName3')
and
t.name like '%YourSearchDataType%')
order by tbl.name
根据一个数据库中三个不同表的搜索数据类型,搜索哪个列在哪个表中。这个查询可以扩展到'n'个表。
查找数据类型和长度的组合结果,并以“NULL”和“非空”的形式为空。
SELECT c.name AS 'Column Name',
t.name + '(' + cast(c.max_length as varchar(50)) + ')' As 'DataType',
case
WHEN c.is_nullable = 0 then 'null' else 'not null'
END AS 'Constraint'
FROM sys.columns c
JOIN sys.types t
ON c.user_type_id = t.user_type_id
WHERE c.object_id = Object_id('TableName')
你会发现结果如下所示。
谢谢你!
试试这个:
select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS IC
where TABLE_NAME = 'tablename' and COLUMN_NAME = 'columnname'
这里没有主键,但这可以帮助其他只想拥有一个包含字段名和基本字段属性的表名的用户
USE [**YourDB**]
GO
SELECT tbl.name, fld.[Column Name],fld.[Constraint],fld.DataType
FROM sys.all_objects as tbl left join
(SELECT c.OBJECT_ID, c.name AS 'Column Name',
t.name + '(' + cast(c.max_length as varchar(50)) + ')' As 'DataType',
case
WHEN c.is_nullable = 0 then 'null' else 'not null'
END AS 'Constraint'
FROM sys.columns c
JOIN sys.types t
ON c.user_type_id = t.user_type_id
) as fld on tbl.OBJECT_ID = fld.OBJECT_ID
WHERE ( tbl.[type]='U' and tbl.[is_ms_shipped] = 0)
ORDER BY tbl.[name],fld.[Column Name]
GO
我刚刚做了marc_s“presentation ready”:
SELECT
c.name 'Column Name',
t.name 'Data type',
IIF(t.name = 'nvarchar', c.max_length / 2, c.max_length) 'Max Length',
c.precision 'Precision',
c.scale 'Scale',
IIF(c.is_nullable = 0, 'No', 'Yes') 'Nullable',
IIF(ISNULL(i.is_primary_key, 0) = 0, 'No', 'Yes') 'Primary Key'
FROM
sys.columns c
INNER JOIN
sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN
sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
c.object_id = OBJECT_ID('YourTableName')