将SQL SERVER中所有表的列信息显示出来
正在作一个关于SQL SERVER数据库导入Excel文件的程序，要读取数据库中的列的信息，从网上找了很多资料，终于总结出来比较理想的sql语句，执行后返回的列分别是：表名、列名、列类型、列长度、列描述、是否主键，语句如下：
SELECT
SysObjects.Name
as
tb_name, SysColumns.Name
as
col_name
, SysTypes.Name
as
col_type, SysColumns.Length
as
col_len,
isnull
(SysProperties.Value,SysColumns.Name)
as
col_memo,
case
when
SysColumns.name
in
(
select
主键
=
a.name
FROM
syscolumns a
inner
join
sysobjects b
on
a.id
=
b.id
and
b.xtype
=
'
U
'
and
b.name
<>
'
dtproperties
'
where
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 )))
and
b.name
=
SysObjects.Name )
then
1
else
0
end
as
is_key
FROM
SysObjects,SysTypes,SysColumns
LEFT
JOIN
SysProperties
ON
(Syscolumns.Id
=
Sysproperties.Id
AND
Syscolumns.Colid
=
Sysproperties.Smallid)
WHERE
(Sysobjects.Xtype
=
'
u
'
OR
Sysobjects.Xtype
=
'
v
'
)
AND
Sysobjects.Id
=
Syscolumns.Id
AND
SysTypes.XType
=
Syscolumns.XType
AND
SysTypes.Name
<>
'
sysname
'
AND
Sysobjects.Name
Like
'
%
'
ORDER
By
SysObjects.Name, SysColumns.colid
如果只想操作一个表的，那么把最后的％换成表名就可以了。
