SqlServer常用语句

?

1 2 3 4 5

--添加表描述 EXECUTE sp_addextendedproperty 'C', '课程表', 'user', 'dbo', 'table', 'Course',NULL, NULL;       --添加字段描述 EXECUTE sp_addextendedproperty 'MS_Description', '课程ID', 'user', 'dbo','table', 'Course', 'column', 'CourseId';

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

--快速查看表结构 SELECT  CASE WHEN col.colorder = 1 THEN obj.name                   ELSE ''              END AS 表名,         col.colorder AS 序号 ,         col.name AS 列名 ,         ISNULL(ep.[value], '') AS 列说明 ,         t.name AS 数据类型 ,         col.length AS 长度 ,         ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数 ,         CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '√'              ELSE ''         END AS 标识 ,         CASE WHEN EXISTS ( SELECT   1                            FROM     dbo.sysindexes si                                     INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id                                                               AND si.indid = sik.indid                                     INNER JOIN dbo.syscolumns sc ON sc.id = sik.id                                                               AND sc.colid = sik.colid                                     INNER JOIN dbo.sysobjects so ON so.name = si.name                                                               AND so.xtype = 'PK'                            WHERE    sc.id = col.id                                     AND sc.colid = col.colid ) THEN '√'              ELSE ''         END AS 主键 ,         CASE WHEN col.isnullable = 1 THEN '√'              ELSE ''         END AS 允许空 ,         ISNULL(comm.text, '') AS 默认值 FROM    dbo.syscolumns col         LEFT  JOIN dbo.systypes t ON col.xtype = t.xusertype         inner JOIN dbo.sysobjects obj ON col.id = obj.id                                          AND obj.xtype = 'U'                                          AND obj.status >= 0         LEFT  JOIN dbo.syscomments comm ON col.cdefault = comm.id         LEFT  JOIN sys.extended_properties ep ON col.id = ep.major_id                                                       AND col.colid = ep.minor_id                                                       AND ep.name = 'MS_Description'         LEFT  JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id                                                          AND epTwo.minor_id = 0                                                          AND epTwo.name = 'MS_Description' WHERE   obj.name = 'PP_Procedure'--表名 ORDER BY col.colorder ;

　　

?

1	-- 查询所有数据库<br><br>select database_id AS Id,name AS Name from sys.databases WHERE name NOT IN( 'master', 'model', 'msdb', 'tempdb', 'northwind','pubs' )<br>

　　

?

1 2	-- 查询所有表 SELECT id As Id,name As Name FROM " + dbInfo.Name + "..SysObjects Where XType='U' ORDER BY Name