我需要在SQL server上编写一个查询,以获得特定表中的列列表,其相关数据类型(长度)以及它们是否不为空。我已经做到了这么多。
但现在我还需要得到,在同一个表中,对一个列- TRUE,如果该列是一个主键。
我该怎么做呢?
我的期望输出是:
Column name | Data type | Length | isnull | Pk
我需要在SQL server上编写一个查询,以获得特定表中的列列表,其相关数据类型(长度)以及它们是否不为空。我已经做到了这么多。
但现在我还需要得到,在同一个表中,对一个列- TRUE,如果该列是一个主键。
我该怎么做呢?
我的期望输出是:
Column name | Data type | Length | isnull | Pk
当前回答
抛出了另一种在SQL server中解决问题的方法。 我的小脚本在这里应该返回列名,数据类型,是空的,约束和索引名称。 您还可以包括任何额外的列,如精度,比例… (你需要用你的数据库名、Schema name和Table name替换数据库名) .返回列的顺序与从'select * from table'中返回列的顺序相同
USE DBA -- Replace Database Name with yours
DECLARE @SCHEMA VARCHAR(MAX)
DECLARE @TABLE_NAME VARCHAR(MAX)
DECLARE @SCHEMA_TABLE_NAME VARCHAR(MAX)
SET @SCHEMA = REPLACE(REPLACE('[SCHEMA NAME]', '[', ''), ']', '')--Replace Schema Name with yours
SET @TABLE_NAME = REPLACE(REPLACE('[TABLE NAME]', '[', ''), ']', '') --' Replace Table Name with yours
SET @SCHEMA_TABLE_NAME = @SCHEMA + '.' + @TABLE_NAME;
WITH SchemaColumns
AS (
SELECT C.COLUMN_NAME,
IS_NULLABLE,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
C.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS AS C
WHERE C.TABLE_SCHEMA = @SCHEMA
AND C.TABLE_NAME = @TABLE_NAME
),
SchemaConstraints
AS (
SELECT CN.COLUMN_NAME,
CC.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS CC
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CN ON CC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
WHERE CC.TABLE_SCHEMA = @SCHEMA
AND CC.TABLE_NAME = @TABLE_NAME
),
SchemaIndex
AS (
SELECT I.name AS index_name,
COL_NAME(IC.object_id, IC.column_id) AS column_name,
IC.index_column_id,
IC.key_ordinal,
IC.is_included_column
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS IC ON I.object_id = IC.object_id
AND I.index_id = IC.index_id
WHERE I.object_id = OBJECT_ID(@SCHEMA_TABLE_NAME)
)
SELECT ISNULL(SchemaColumns.COLUMN_NAME, '') "Column Name",
CASE
WHEN SchemaColumns.CHARACTER_MAXIMUM_LENGTH IS NULL
THEN UPPER(ISNULL(SchemaColumns.DATA_TYPE, ''))
ELSE CONCAT (
UPPER(ISNULL(SchemaColumns.DATA_TYPE, '')),
'(',
CAST(SchemaColumns.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(50)),
')'
)
END "Data Type",
SchemaColumns.IS_NULLABLE "Is Nullable",
ISNULL(SchemaConstraints.CONSTRAINT_TYPE, '-') "Constraints",
ISNULL(STRING_AGG(CONVERT(NVARCHAR(max), SchemaIndex.INDEX_NAME), CHAR(13)), '-') "Indexes Names"
FROM SchemaColumns
LEFT JOIN SchemaConstraints ON SchemaConstraints.COLUMN_NAME = SchemaColumns.COLUMN_NAME
LEFT JOIN SchemaIndex ON SchemaColumns.COLUMN_NAME = SchemaIndex.COLUMN_NAME
GROUP BY SchemaColumns.COLUMN_NAME,
SchemaColumns.DATA_TYPE,
SchemaColumns.CHARACTER_MAXIMUM_LENGTH,
SchemaColumns.IS_NULLABLE,
SchemaConstraints.CONSTRAINT_TYPE,
SchemaColumns.ORDINAL_POSITION
ORDER BY SchemaColumns.ORDINAL_POSITION
其他回答
我刚刚做了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')
这里没有主键,但这可以帮助其他只想拥有一个包含字段名和基本字段属性的表名的用户
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
为了避免某些列的重复行,请使用user_type_id而不是system_type_id。
SELECT
c.name 'Column Name',
t.Name 'Data type',
c.max_length 'Max Length',
c.precision ,
c.scale ,
c.is_nullable,
ISNULL(i.is_primary_key, 0) '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')
只需将YourTableName替换为实际的表名-适用于SQL Server 2005及更高版本。
如果您正在使用模式,请将YourTableName替换为YourSchemaName。YourTableName,其中YourSchemaName是实际的模式名,YourTableName是实际的表名。
我有点惊讶居然没人提
sp_help 'mytable'
SELECT
T.NAME AS [TABLE NAME]
,C.NAME AS [COLUMN NAME]
,P.NAME AS [DATA TYPE]
,P.MAX_LENGTH AS [Max_SIZE]
,C.[max_length] AS [ActualSizeUsed]
,CAST(P.PRECISION AS VARCHAR) +'/'+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE]
FROM SYS.OBJECTS AS T
JOIN SYS.COLUMNS AS C
ON T.OBJECT_ID = C.OBJECT_ID
JOIN SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID = P.SYSTEM_TYPE_ID
AND C.[user_type_id] = P.[user_type_id]
WHERE T.TYPE_DESC='USER_TABLE'
AND T.name = 'InventoryStatus'
ORDER BY 2