sqlserver text 转varchar_数据库干货：整理SQLServer非常实用的脚本

今天给大家分享自己在工作当中用到的SQLServer一些常用的脚本，希望能对大家有所帮助！

1、 查询数据库所有表结构

通过该脚本可以快速查找表字段，或者生成数据库设计文档、进行数据库对比。

SELECT obj.name 表名,col.colorder AS 序号 ,col.name AS 列名 ,ISNULL(ep.[value], '') AS 列说明 ,t.name AS 数据类型 ,CASE WHEN col.isnullable = 1 THEN '1'ELSE ''END AS 允许空 ,ISNULL(comm.text, '') AS 默认值,Coalesce(epTwo.value, '') AS documentationFROM dbo.syscolumns colLEFT JOIN dbo.systypes t ON col.xtype = t.xusertypeinner JOIN dbo.sysobjects obj ON col.id = obj.idAND obj.xtype = 'U'AND obj.status >= 0LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.idLEFT JOIN sys.extended_properties ep ON col.id = ep.major_idAND col.colid = ep.minor_idAND ep.name = 'MS_Description'LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_idAND epTwo.minor_id = 0AND epTwo.name = 'MS_Description'WHERE obj.name in(SELECTob.name FROM sys.objects AS obLEFT OUTER JOIN sys.extended_properties AS epON ep.major_id = ob.object_idAND ep.class = 1AND ep.minor_id = 0WH