sql server 2005
-- 1. 表结构信息查询
-- ========================================================================
-- 表结构信息查询
-- 邹建 2005.08(引用请保留此信息)
-- ========================================================================
SELECT
TableName=CASE WHEN C.column_id
=1 THEN O.name
ELSE N
‘‘ END,
TableDesc=ISNULL(
CASE WHEN C.column_id
=1 THEN PTB.
[value] END,N
‘‘),
Column_id=C.column_id,
ColumnName=C.name,
PrimaryKey=ISNULL(IDX.PrimaryKey,N
‘‘),
[IDENTITY]=CASE WHEN C.is_identity
=1 THEN N
‘√‘ELSE N
‘‘ END,
Computed=CASE WHEN C.is_computed
=1 THEN N
‘√‘ELSE N
‘‘ END,
Type=T.name,
Length=C.max_length,
Precision=C.
precision,
Scale=C.scale,
NullAble=CASE WHEN C.is_nullable
=1 THEN N
‘√‘ELSE N
‘‘ END,
[Default]=ISNULL(D.definition,N
‘‘),
ColumnDesc=ISNULL(PFD.
[value],N
‘‘),
IndexName=ISNULL(IDX.IndexName,N
‘‘),
IndexSort=ISNULL(IDX.Sort,N
‘‘),
Create_Date=O.Create_Date,
Modify_Date=O.Modify_date
FROM sys.columns C
INNER JOIN sys.objects O
ON C.
[object_id]=O.
[object_id]
AND O.type
=‘U‘
AND O.is_ms_shipped
=0
INNER JOIN sys.types T
ON C.user_type_id
=T.user_type_id
LEFT JOIN sys.default_constraints D
ON C.
[object_id]=D.parent_object_id
AND C.column_id
=D.parent_column_id
AND C.default_object_id
=D.
[object_id]
LEFT JOIN sys.extended_properties PFD
ON PFD.class
=1
AND C.
[object_id]=PFD.major_id
AND C.column_id
=PFD.minor_id
-- AND PFD.name=‘Caption‘ -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)
LEFT JOIN sys.extended_properties PTB
ON PTB.class
=1
AND PTB.minor_id
=0
AND C.
[object_id]=PTB.major_id
-- AND PFD.name=‘Caption‘ -- 表说明对应的描述名称(一个表可以添加多个不同name的描述)
LEFT JOIN -- 索引及主键信息
(
SELECT
IDXC.[object_id],
IDXC.column_id,
Sort=CASE INDEXKEY_PROPERTY(IDXC.
[object_id],IDXC.index_id,IDXC.index_column_id,
‘IsDescending‘)
WHEN 1 THEN ‘DESC‘ WHEN 0 THEN ‘ASC‘ ELSE ‘‘ END,
PrimaryKey=CASE WHEN IDX.is_primary_key
=1 THEN N
‘√‘ELSE N
‘‘ END,
IndexName=IDX.Name
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC
ON IDX.
[object_id]=IDXC.
[object_id]
AND IDX.index_id
=IDXC.index_id
LEFT JOIN sys.key_constraints KC
ON IDX.
[object_id]=KC.
[parent_object_id]
AND IDX.index_id
=KC.unique_index_id
INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息
(
SELECT [object_id], Column_id, index_id
=MIN(index_id)
FROM sys.index_columns
GROUP BY [object_id], Column_id
) IDXCUQ
ON IDXC.
[object_id]=IDXCUQ.
[object_id]
AND IDXC.Column_id
=IDXCUQ.Column_id
AND IDXC.index_id
=IDXCUQ.index_id
) IDX
ON C.
[object_id]=IDX.
[object_id]
AND C.column_id
=IDX.column_id
WHERE --O.name=N‘UC_Admin‘ -- 如果只查询指定表,加上此条件
O.name
=(
case when len(
@name)
>0
then @name
else
O.name
end
)
ORDER BY O.name,C.column_id
SELECT TableName = CASE WHEN a.colorder=1 THEN d.name
ELSE d.name
END
,TableDesc = CASE WHEN a.colorder=1 THEN ISNULL(f.value,‘‘)
ELSE ISNULL(f.value,‘‘)
END
,Column_id = a.colorder
,ColumnName = a.name
,IDENTITY = CASE WHEN COLUMNPROPERTY(a.id,a.name,‘IsIdentity‘)=1
THEN ‘√‘
ELSE ‘ב
END
,PrimarKey = CASE WHEN EXISTS ( SELECT 1
FROM sysobjects
WHERE xtype=‘PK‘
AND name IN (
SELECT name
FROM sysindexes
WHERE indid IN (
SELECT indid
FROM sysindexkeys
WHERE id=a.id
AND colid=a.colid)) )
THEN ‘√‘
ELSE ‘ב
END
,TypeName = b.name
,ByteLength = a.length
,Length = COLUMNPROPERTY(a.id,a.name,‘PRECISION‘)
,Scale = ISNULL(COLUMNPROPERTY(a.id,a.name,‘Scale‘),0)
,NullAble = CASE WHEN a.isnullable=1 THEN ‘√‘
ELSE ‘ב
END
,[Default] = ISNULL(e.text,‘‘)
,ColumnDesc = ISNULL(g.[value],‘‘)
FROM syscolumns a
LEFT JOIN systypes b
ON a.xusertype=b.xusertype
INNER JOIN sysobjects d
ON a.id=d.id
AND d.xtype=‘U‘
AND d.name<>‘dtproperties‘
LEFT JOIN syscomments e
ON a.cdefault=e.id
LEFT JOIN sys.extended_properties g
ON a.id=g.major_id
AND a.colid=g.minor_id
LEFT JOIN sys.extended_properties f
ON d.id=f.major_id
AND f.minor_id=0 --
where --d.name=‘UC_Admin‘ --如果只查询指定表,加上此条件
d.name=(
case when len(@name)>0
then @name
else
d.name
end
)
ORDER BY a.id,a.colorder;
视图
select
TableName= O.name
,TableDesc=O.name
,Column_id=c.column_id
,ColumnName=c.name
,PrimaryKey=0
,[IDENTITY]=0
,Computed=0
,TypeName=T.name
,[Length]=C.max_length
,[Precision]=c.precision
,Scale=c.scale
,NullAble=0
,[Default]=‘‘
,ColumnDesc=c.name
,IndexName=‘‘
,IndexSort=‘‘
,Create_Date=O.Create_Date
,Modify_Date=O.Modify_date
from sys.columns c
left join sys.types T ON C.user_type_id=T.user_type_id
left JOIN sys.objects O ON C.[object_id]=O.[object_id] AND O.type=‘V‘
WHERE --O.name=‘View_Staff_Score_Log‘ and -- 如果只查询指定表,加上此条件
O.name=(
case when len(@name)>0
then @name
else
O.name
end
)
SQL表结构
标签：text exe type from com nbsp and name end
