查看数据库版本
SELECT @@VERSION;
查看库下的表和字段
SELECT BB.序号, CASE WHEN BB.序号 = 0 THEN CONCAT('表名称:', BB.表名) ELSE BB.字段名称 END AS '字段名称', BB.字段类型, BB.Size, BB.主键, BB.自增, BB.可空, BB.默认值, BB.字段说明 FROM( SELECT ROW_NUMBER() over(PARTITION BY AA.表名 ORDER BY AA.字段名称) - 1 AS '序号', AA.表名, AA.字段名称, AA.字段类型, AA.Size, AA.主键, AA.自增, AA .可空, AA.默认值, AA.字段说明 FROM( SELECT DISTINCT OBJ.NAME AS '表名', NULL AS 字段名称, NULL AS 字段类型, NULL AS 'Size', NULL AS 主键, NULL AS 自增, NULL AS 可空, NULL AS 默认值, NULL AS 字段说明 FROM syscolumns col LEFT JOIN systypes t ON col.xtype = t.xusertype inner JOIN sysobjects obj ON col.id = obj.id AND obj .xtype = 'U' AND obj.status >= 0 LEFT JOIN 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' UNION ALL SELECT OBJ.NAME AS '表名', col.name AS 字段名称, t.name AS 字段类型, col.length AS 'Size', CASE WHEN EXISTS(SELECT 1 FROM sysindexes si INNER JOIN sysindexkeys sik ON si .id = sik.id AND si.indid = sik.indid INNER JOIN syscolumns sc ON sc .id = sik.id AND sc.colid = sik.colid INNER JOIN 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 COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 自增, CASE WHEN col.isnullable = 1 THEN '√' ELSE '' END AS 可空, ISNULL(comm.text, '') AS 默认值, ISNULL(ep.[value], '') AS 字段说明 FROM syscolumns col LEFT JOIN systypes t ON col .xtype = t.xusertype inner JOIN sysobjects obj ON col.id = obj.id AND obj.xtype = 'U' AND obj.status >= 0 LEFT JOIN 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' ) AS AA ) AS BB
查看库下的所有表信息
SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY OBJ.NAME) AS '序号', OBJ.NAME AS '表名', (SELECT EPT.VALUE FROM SYS.EXTENDED_PROPERTIES EPT WHERE EPT.MAJOR_ID = OBJ.ID AND EPT.MINOR_ID = 0) AS '注释/说明' FROM SYSOBJECTS OBJ WHERE 1=1 AND OBJ.XTYPE = 'U' AND OBJ.STATUS >= 0 AND OBJ.NAME <> 'DTPROPERTIES'
存储过程是否存在
select count(*) from sysobjects where id = object_id(N'存储过程名称') and OBJECTPROPERTY(id, N'IsProcedure') = 1;-- 返回结果是1则表示存在
递归实现tree结构
WITH RecursiveOrg AS ( SELECT Unique_No, Parent_Unique_No FROM basic WHERE Parent_Unique_No = '最高级别的父级唯一标识' UNION ALL SELECT o.Unique_No, o.Parent_Unique_No FROM basic o INNER JOIN RecursiveOrg r ON o.Parent_Unique_No = r.Unique_No ) SELECT r.Unique_No, r.Parent_Unique_No FROM Recursion r WHERE 1 = 1
OrderBy使用中文排序
Chinese_PRC指的是中国大陆地区，如果是台湾地区则为Chinese_TaiwanCI指定不区分大小写，如果要在查询时区分输入的大小写则改为CSAS指定区分重音，同样如果不需要区分重音，则改为AI
排序规则
ORDER BY 字段名 COLLATE Chinese_PRC_CI_AS
重置ID,从1开始
dbcc checkident('表名',reseed,0)
评论区