Go homepage(回首页) Upload pictures (上传图片) Write articles (发文字帖)
The author:(作者)归海一刀published in(发表于) 2014/2/1 0:22:01 查询数据库表和字段sql语句_[SQL,Server教程]
今天整理资料时看到有这样一个查询数据库中的表和字段信息的语句,很强! 就是忘了当初谁写的了,印像中该是邹建所创。也贴出来以后备用吧。
1SELECT 2表名 = CASE a.colorder WHEN 1 THEN c.name ELSE '' END, 3序 = a.colorder, 4字段名 = a.name, 5标识 = CASE COLUMNPROPERTY(a.id,a.name,'IsIdentity') WHEN 1 THEN '√' ELSE '' END, 6主键 = CASE 7WHEN EXISTS ( 8SELECT * 9FROM sysobjects10WHERE xtype='PK' AND name IN (11SELECT name12FROM sysindexes13WHERE id=a.id AND indid IN (14SELECT indid15FROM sysindexkeys16WHERE id=a.id AND colid IN (17SELECT colid18FROM syscolumns19WHERE id=a.id AND name=a.name20)21)22)23)24THEN '√'25ELSE ''26END,27类型 = b.name,28字节数 = a.length,29长度 = COLUMNPROPERTY(a.id,a.name,'Precision'),30小数 = CASE ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0)31WHEN 0 THEN ''32ELSE CAST(COLUMNPROPERTY(a.id,a.name,'Scale') AS VARCHAR)33END,34允许空 = CASE a.isnullable WHEN 1 THEN '√' ELSE '' END,35默认值 = ISNULL(d.[text],''),36说明 = ISNULL(e.[value],'')37FROM syscolumns a38LEFT JOIN systypes b ON a.xtype=b.xusertype39INNER JOIN sysobjects c ON a.id=c.id AND c.xtype='U' AND c.name<>'dtproperties'40LEFT JOIN syscomments d ON a.cdefault=d.id41LEFT JOIN sysproperties e ON a.id=e.id AND a.colid=e.smallid42ORDER BY c.name, a.colorder
我修改一下,变个精简版本的:
12select a.name, b.xtype,b.name3from syscolumns a4inner JOIN systypes b5ON a.xtype=b.xusertype6inner join sysobjects c ON7a.id=c.id AND c.xtype='U' AND c.name<>'dtproperties' where c.name = 表名
来源:网络
赞