[求助]sql server 查询列名、列数据类型、列长度 sql语句怎么写

sql server 查询列名、列数据类型、列长度 sql语句怎么写？

25 个解决方案

#1

select
    o.name as tbname,
    c.name as columnname,
    t.name as typename,
    c.max_length
from sys.tables as o
    join sys.columns as c
on o.object_id=c.object_id
    join sys.types as t
on c.system_type_id=t.system_type_id

#2

计算一个库里各个表的记录总数：
select b.name,a.rowcnt from sysindexes a,sysobjects b 
where a.id=b.id and a.indid<2 and b.xtype='u'

--统计数据库里每个表的详细情况
   EXEC sp_MSforeachtable @command1="sp_spaceused '?'"

   --获得每个表的记录数和容量:
   EXEC sp_MSforeachtable @command1="print '?'",
        @command2="sp_spaceused '?'",
        @command3= "SELECT count(*) FROM ? "

#3

select
    o.name as tbname,
    c.name as columnname,
    t.name as typename,
    c.max_length
from sys.tables as o
    join sys.columns as c
on o.object_id=c.object_id
    join sys.types as t
on c.system_type_id=t.system_type_id

#4

	SELECT
		C.column_id
		,C.name
		,type	= T.name + CASE T.user_type_id WHEN 41 THEN '('+CAST(C.scale AS VARCHAR) +')'	-- time
			WHEN 42 THEN '('+CAST(C.scale AS VARCHAR) +')'										-- datetime2
			WHEN 43 THEN '('+CAST(C.scale AS VARCHAR) +')'										-- datetimeoffset
			WHEN 106 THEN '('+CAST(C.precision  AS VARCHAR)+','+ CAST(C.scale AS VARCHAR) +')'	-- decimal
			WHEN 108 THEN '('+CAST(C.precision  AS VARCHAR)+','+ CAST(C.scale AS VARCHAR) +')'	-- numeric
			WHEN 165 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')'		-- varbinary
			WHEN 167 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')'		-- varchar
			WHEN 173 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')'		-- binary
			WHEN 175 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')'		-- char
			WHEN 231 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')'		-- nvarchar
			WHEN 239 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')'		-- nchar
			ELSE ''
			END
		,C.is_identity
		,C.is_nullable
		,DefaultText = ISNULL(STUFF(LEFT(D.definition,LEN(D.definition)-1),1,1,''),'')
	FROM sys.columns C
	INNER JOIN sys.types T ON C.user_type_id = T.user_type_id 
	LEFT JOIN sys.default_constraints D ON D.[object_id] =C.default_object_id 
		AND D.parent_object_id = C.[object_id] AND D.parent_column_id = C.column_id 
	WHERE C.[object_id] = OBJECT_ID('你的表名');

#5

海爷.你吓到我了.

#6

说明：列出数据库里所有的表名 
select name from sysobjects where type='U' 

说明：列出表里的所有的列 
select name from syscolumns where id=object_id('TableName') 

#7

	SELECT
		C.column_id
		,C.name
		,type	= T.name + CASE T.user_type_id WHEN 41 THEN '('+CAST(C.scale AS VARCHAR) +')'	-- time
			WHEN 42 THEN '('+CAST(C.scale AS VARCHAR) +')'										-- datetime2
			WHEN 43 THEN '('+CAST(C.scale AS VARCHAR) +')'										-- datetimeoffset
			WHEN 106 THEN '('+CAST(C.precision  AS VARCHAR)+','+ CAST(C.scale AS VARCHAR) +')'	-- decimal
			WHEN 108 THEN '('+CAST(C.precision  AS VARCHAR)+','+ CAST(C.scale AS VARCHAR) +')'	-- numeric
			WHEN 165 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')'		-- varbinary
			WHEN 167 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')'		-- varchar
			WHEN 173 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')'		-- binary
			WHEN 175 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')'		-- char
			WHEN 231 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')'		-- nvarchar
			WHEN 239 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')'		-- nchar
			ELSE ''
			END
		,C.is_identity
		,C.is_nullable
		,DefaultText = ISNULL(STUFF(LEFT(D.definition,LEN(D.definition)-1),1,1,''),'')
	FROM sys.columns C
	INNER JOIN sys.types T ON C.user_type_id = T.user_type_id 
	LEFT JOIN sys.default_constraints D ON D.[object_id] =C.default_object_id 
		AND D.parent_object_id = C.[object_id] AND D.parent_column_id = C.column_id 
	WHERE C.[object_id] = OBJECT_ID('你的表名');

#8

SELECT a.colid as ID,a.name as ColumnName,b.name as DataType,a.length as Length FROM syscolumns a,systypes b WHERE a.id=object_id('相应表名') and a.xtype=b.xtype and b.name <> 'sysname' order by a.colid 

#9

海爷.你吓到我了.

#10

引用 2 楼 fredrickhu 的回复:

SQL code计算一个库里各个表的记录总数：select b.name,a.rowcntfrom sysindexes a,sysobjects bwhere a.id=b.idand a.indid<2and b.xtype='u'--统计数据库里每个表的详细情况EXEC sp_MSforeachtable@command1="sp_spaceused'?'"--获得每个表的记录数和容量:EX¡­

顶

正确

#11

select name as colName,type_name(xtype) as colType,length
from syscolumns
where id=object_id('tableName')

#12

SELECT name,Xtype,length FROM syscolumns  WHERE ID= OBJECT_ID('NUM')

2000

#13

7#  啊。。。。。。。我眼前一亮的一楼。

理解错误了 但是太强大了。

#14

用INFORMATION_SCHEMA视图好些。
select * from INFORMATION_SCHEMA.COLUMNS 
  where TABLE_NAME = 'Customer'

东西还是比较全的，而且MS会保持这个视图不变的
相对比较安全

#15

[code=SQL]SELECT 
(CASE WHEN a.colorder=1 THEN d.name ELSE '' END) N'表名',
a.colorder N'字段序号',
a.name N'字段名',
(CASE WHEN COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 THEN '√'ELSE '' END) N'标识',
(CASE WHEN (SELECT COUNT(*)
FROM sysobjects
WHERE (name in
            (SELECT name
           FROM sysindexes
           WHERE (id = a.id) AND (indid in
                     (SELECT indid
                    FROM sysindexkeys
                    WHERE (id = a.id) AND (colid in
                              (SELECT colid
                             FROM syscolumns
                             WHERE (id = a.id) AND (name = a.name))))))) AND
         (xtype = 'PK'))>0 THEN '√' ELSE '' END) N'主键',
b.name N'类型',
a.length N'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION') AS N'长度',
ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0) AS N'小数位数',
(CASE WHEN a.isnullable=1 THEN '√'ELSE '' END) N'允许空',
ISNULL(e.text,'') N'默认值',
ISNULL(g.[value],'') AS N'字段说明'

FROM syscolumns a 
LEFT JOIN systypes b on a.xtype=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 sysproperties g on a.id=g.id AND a.colid=g.smallid  
--WHERE 
--d.name = '表名' --要查询的表
ORDER BY 
object_name(a.id), a.colorder

/*
表名 所有者 字段序号 字段名 是否标识列 主键 类型 占用字节数 长度 小数位数 允许空 默认值 字段说明
sp_pici dbo 1 pici √ varchar 20 20 0 ('')
dbo 2 spid √ char 11 11 0 ('')
dbo 3 pihao varchar 20 20 0 √ ('')
dbo 4 djbh char 15 15 0 √ ('')
dbo 5 rq char 10 10 0 √ ('')
dbo 6 gebjj decimal 9 14 6 √ (0)
dbo 7 hw √ char 11 11 0 ('')
dbo 8 shl decimal 9 14 2 √ (0)
dbo 9 dwbh char 11 11 0 √ ('')
dbo 10 sxrq char 10 10 0 √ ('')
dbo 11 xsshl decimal 9 14 2 √ (0)
dbo 12 is_dp char 2 2 0 √ ('否')
dbo 13 xsqu varchar 100 100 0 √ ('')
dbo 14 isxs char 2 2 0 √ ('否')
dbo 15 jwh char 10 10 0 √ ('')
dbo 16 jwhbz char 50 50 0 √ ('')
dbo 17 baozhiqi char 10 10 0 √ ('')
*/[/code]

#16

好吧我承认我被吓到了

#17

MARK一下回头研究一下海爷的CASE WHEN

#18

declare @table_name as varchar(max) 
set @table_name = '你的表名'
select sys.columns.name, sys.types.name, sys.columns.max_length, sys.columns.is_nullable, 
(select count(*) from sys.identity_columns where sys.identity_columns.object_id = sys.columns.object_id and sys.columns.column_id = sys.identity_columns.column_id) as is_identity , 
(select value from sys.extended_properties where sys.extended_properties.major_id = sys.columns.object_id and sys.extended_properties.minor_id = sys.columns.column_id) as description 
from sys.columns, sys.tables, sys.types where sys.columns.object_id = sys.tables.object_id and sys.columns.system_type_id=sys.types.system_type_id and sys.tables.name=@table_name order by sys.columns.column_id

这个可以看到表中列的详细信息，
很不错的！

#19

你试下看！

#20

--数据库中所有表的信息（很强悍的）

 SELECT   
表名               =   CASE   WHEN   A.COLORDER=1   THEN   D.NAME   ELSE   ' '   END, 
表說明           =   CASE   WHEN   A.COLORDER=1   THEN   ISNULL(F.VALUE, ' ')   ELSE   ' '   END, 
欄位序號       =   A.COLORDER, 
欄位名           =   A.NAME, 
標識               =   CASE   WHEN   COLUMNPROPERTY(   A.ID,A.NAME, 'ISIDENTITY ')=1   THEN   '√ 'ELSE   ' '   END, 
主鍵               =   CASE   WHEN   EXISTS(SELECT   1   FROM   SYSOBJECTS   WHERE   XTYPE= 'PK '   AND   PARENT_OBJ=A.ID   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, 
類型               =   B.NAME, 
佔用位元組數   =   A.LENGTH, 
長度               =   COLUMNPROPERTY(A.ID,A.NAME, 'PRECISION '), 
小數位數       =   ISNULL(COLUMNPROPERTY(A.ID,A.NAME, 'SCALE '),0), 
允許空           =   CASE   WHEN   A.ISNULLABLE=1   THEN   '√ 'ELSE   ' '   END, 
預設值           =   ISNULL(E.TEXT, ' '), 
欄位說明       =   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 
ORDER   BY   
A.ID,A.COLORDER 

再来一个这个把数据库中所有的表的详细信息都给列出来了。

#21

引用 16 楼 guguda2008 的回复:

好吧我承认我被吓到了

有时间看一下MSSQL上的视图定义，呵呵。感觉有点象。

#22

谢谢各位的解答，答案我已经找到了，忘了结贴，今天来补上。
现在回想起来这些东西都在系统表格里，当时还不知道系统表格这个东东。

#23

是什么？

#24

该回复于2011-02-11 08:55:40被版主删除

#25

试用了下liangCK的方法，这样可以查出所有数据库的数据库名、字段和字段对应数据类型以及长度，UP一下！