SQL Server 2008 实用高级查询

xtype:对象类型。可以是下列对象类型中的一种： 

C = CHECK 约束

D = 默认值或 DEFAULT 约束

F = FOREIGN KEY 约束

L = 日志

FN = 标量函数

IF = 内嵌表函数

P = 存储过程

PK = PRIMARY KEY 约束（类型是 K）

RF = 复制筛选存储过程

S = 系统表

TF = 表函数

TR = 触发器

U = 用户表

UQ = UNIQUE 约束（类型是 K）

V = 视图

X = 扩展存储过程

1.用SQL语句查找包含有某个关键字的存储过程、触发器、函数等

--查询包含特定关键字的存储过程与函数
SELECT NAME FROM sysobjects AS s
INNER JOIN syscomments AS s2 ON s2.id = s.id
WHERE TEXT LIKE '%关键字%' and s.xtype = 'P'

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES  
WHERE ROUTINE_DEFINITION LIKE '%关键字%' 
AND ROUTINE_TYPE='PROCEDURE'

--查询存储过程与函数的参数
select * from INFORMATION_SCHEMA.PARAMETERS  

--查询数据库的表与视图
select * from INFORMATION_SCHEMA.TABLES 

2.查询外键关系

SELECT PT.name 引用表名,PC.name 引用列名,RT.name 被引用表名,RC.name 被引用列名
FROM sys.foreign_key_columns JOIN sys.objects PT ON sys.foreign_key_columns.parent_object_id=PT.object_id
JOIN sys.objects RT ON sys.foreign_key_columns.referenced_object_id=RT.object_id
JOIN sys.columns PC ON sys.foreign_key_columns.parent_object_id=PC.object_id AND sys.foreign_key_columns.parent_column_id=PC.column_id
JOIN sys.columns RC ON sys.foreign_key_columns.referenced_object_id=RC.object_id AND sys.foreign_key_columns.referenced_column_id=RC.column_id
where RT.name='Biz_Audit_MaterialBill'

 3.分组后拼接字段

使用STUFF函数，配合 FOR XML PATH 

SELECT * FROM Mobile_Sys_User WHERE UserID=301
SELECT * FROM Mobile_Sys_UserDept WHERE UserID=301
SELECT * FROM Sys_MessageSubscribe_Users WHERE UserID=301
SELECT * FROM Sys_MessageSubscribe_Depts WHERE DeptID=12001

SELECT * FROM(SELECT u.OpenId,u.UserName,
STUFF((SELECT ','+OwnerDepts FROM Mobile_Sys_UserDept WHERE UserID=u.UserID FOR XML PATH('')),1,1,'') AS OwnerDepts 
FROM Sys_MessageSubscribe_Users AS s  
INNER JOIN Mobile_Sys_User AS u ON s.UserID=u.UserID 
INNER JOIN Sys_MessageSubscribe_Depts AS sd ON sd.MessageSubscribeID = s.MessageSubscribeID 
WHERE s.UserFrom='WeiXin' AND  sd.MessageSubscribeID=101 AND sd.DeptID=12001
) AS ul WHERE CHARINDEX('12001', ul.OwnerDepts)>0

 4.查询库里所有表的行数

SELECT a.name as 表名,max(b.rows) as 记录条数 
FROM sysobjects a ,sysindexes b WHERE a.id=b.id AND a.xtype='u'   
GROUP BY a.name ORDER BY max(b.rows) DESC

 5.查询死锁

--查询数据库死锁
--EXEC sp_who_lock

IF EXISTS (
       SELECT *
       FROM   dbo.sysobjects
       WHERE  id = OBJECT_ID(N'[dbo].[sp_who_lock]')
              AND OBJECTPROPERTY(id ,N'IsProcedure') = 1
   )
    DROP PROCEDURE [dbo].[sp_who_lock]
GO

USE MASTER
GO
CREATE PROCEDURE sp_who_lock
AS
BEGIN
    DECLARE @spid                           INT
           ,@bl                             INT
           ,@intTransactionCountOnEntry     INT
           ,@intRowcount                    INT
           ,@intCountProperties             INT
           ,@intCounter                     INT
    
    CREATE TABLE #tmp_lock_who
    (
        id       INT IDENTITY(1 ,1)
       ,spid     SMALLINT
       ,bl       SMALLINT
    )
    IF @@ERROR <> 0
        RETURN @@ERROR
    
    INSERT INTO #tmp_lock_who
      (
        spid
       ,bl
      )
    SELECT 0
          ,blocked
    FROM   (
               SELECT *
               FROM   sysprocesses
               WHERE  blocked > 0
           ) a
    WHERE  NOT EXISTS(
               SELECT *
               FROM   (
                          SELECT *
                          FROM   sysprocesses
                          WHERE  blocked > 0
                      ) b
               WHERE  a.blocked = spid
           )
    UNION
    SELECT spid
          ,blocked
    FROM   sysprocesses
    WHERE  blocked > 0
    
    IF @@ERROR <> 0
        RETURN @@ERROR
    -- 找到临时表的记录数
    SELECT @intCountProperties = COUNT(*)
          ,@intCounter = 1
    FROM   #tmp_lock_who
    
    IF @@ERROR <> 0
        RETURN @@ERROR
    
    IF @intCountProperties = 0
        SELECT '现在没有阻塞和死锁信息' AS MESSAGE
    -- 循环开始
    WHILE @intCounter <= @intCountProperties
    BEGIN
        -- 取第一条记录
        SELECT @spid = spid
              ,@bl     = bl
        FROM   #tmp_lock_who
        WHERE  Id      = @intCounter
        
        BEGIN
            IF @spid = 0
                SELECT '引起数据库死锁的是: ' + CAST(@bl AS VARCHAR(10))
                       + '进程号,其执行的SQL语法如下'
            ELSE
                SELECT '进程号SPID：' + CAST(@spid AS VARCHAR(10)) + '被' + 
                       '进程号SPID：' + CAST(@bl AS VARCHAR(10)) + 
                       '阻塞,其当前进程执行的SQL语法如下'
            
            DBCC INPUTBUFFER(@bl)
        END
        -- 循环指针下移
        SET @intCounter = @intCounter + 1
    END
    DROP TABLE #tmp_lock_who
    RETURN 0
END

 6.查看表结构

--查看表结构
CREATE PROC CheckTableStructure   
@tableName VARCHAR(50)  
AS  
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 = @tableName
ORDER BY
       col.colorder; 

7.创建表视图

--创建表视图 
CREATE PROC CreateTableView   
@tableName VARCHAR(50)    
AS    
    
DECLARE @sql VARCHAR(MAX)    
    
SET @sql = 'create View ' + @tableName + '_V' + ' as ' + 'SELECT ' + STUFF(
        (
            SELECT ',' + col.name + ' AS ' + CONVERT(VARCHAR(50) ,ISNULL(ep.[value] ,''))
            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 = @tableName--表名
            ORDER BY
                   col.colorder 
                   FOR XML PATH('')
        )
       ,1
       ,1
       ,''
    ) + ' FROM ' + @tableName    
PRINT @sql

8. 查询包含指定列(字段)的所有表

SELECT B.name AS TableName,A.name as columnname  
From syscolumns AS A  
INNER JOIN sysobjects AS B ON A.id=B.id    
WHERE b.type='U' AND A.name='ProdNo'

9. 查询包含指定数据的所有表及字段

DECLARE @str NVARCHAR(10)
DECLARE @tablename VARCHAR(50)
DECLARE @colname VARCHAR(50)
DECLARE @counts INT
DECLARE @sql NVARCHAR(2000)
DECLARE cur1 CURSOR  
FOR
    SELECT a.name          tablename,
           B.name          colname
    FROM   sys.objects     a,
           syscolumns      b
    WHERE  a.object_id = b.id
           AND a.type_desc = 'USER_TABLE'

SET @str = '菁' 
OPEN cur1 
FETCH NEXT FROM cur1 INTO @tablename,@colname
WHILE (@@Fetch_Status = 0)
BEGIN
    SET @sql = N'select  @counts=COUNT(*) from ' + @tablename + 
        '   where  charindex(''' + @str + ''',' + @colname + ')>0'
    
    EXEC sp_executesql @sql,
         N'@counts int output',
         @counts OUTPUT
    
    IF @counts > 0
    BEGIN
        PRINT @tablename + ',' + @colname
    END
    
    FETCH NEXT FROM cur1 INTO @tablename,@colname
END
CLOSE cur1 
DEALLOCATE cur1 

10. 查询所有用户表的所有字段的类型及长度

SELECT SNAME.NAME,SCOL.NAME,STYPE.NAME,STYPE.MAX_LENGTH
FROM SYSOBJECTS SNAME, sys.all_columns SCOL,sys.types STYPE WHERE SNAME.TYPE='U' AND
SCOL.OBJECT_ID=SNAME.ID AND STYPE.SYSTEM_TYPE_ID=SCOL.SYSTEM_TYPE_ID
ORDER BY SNAME.NAME

 11.查询重复项

select * from tablename where id in (select id from tablename group by id having count(id) > 1)

 12.行转列

SELECT b.NetWeight,MaterialType=ISNULL((select TOP 1 NAME from  Biz_Stuff_MaterialType AS bsmt 
INNER JOIN Biz_Stuff_MaterialSpec AS bsms ON bsms.MaterialTypeID = bsmt.MaterialTypeID 
                     WHERE bsms.MaterialName=b.MaterialName),'未知')
FROM dbo.Biz_Audit_MateriaInvoicingMonItem_Actual as b
INNER JOIN dbo.Biz_Audit_MateriaInvoicingMon as a ON a.MateriaInvoicingMonID=b.MateriaInvoicingMonID 
WHERE b.NetWeight<>0 AND b.MateriaPrice<>0 AND a.SiteID=10001 and a.AuditMon='2018-06'
GO

SELECT * FROM (
SELECT b.NetWeight,MaterialType=ISNULL((select TOP 1 NAME from  Biz_Stuff_MaterialType AS bsmt 
INNER JOIN Biz_Stuff_MaterialSpec AS bsms ON bsms.MaterialTypeID = bsmt.MaterialTypeID 
                     WHERE bsms.MaterialName=b.MaterialName),'未知')
FROM dbo.Biz_Audit_MateriaInvoicingMonItem_Actual as b
INNER JOIN dbo.Biz_Audit_MateriaInvoicingMon as a ON a.MateriaInvoicingMonID=b.MateriaInvoicingMonID 
WHERE a.SiteID=10001 and a.AuditMon='2018-06'
) AS A 
PIVOT (
    SUM(A.NetWeight) FOR 
    A.[MaterialType] IN ([水泥],[砂],[石],[粉煤灰],[矿粉],[外加剂],[其它],[未知])
) AS T 

 13.查询数据库CPU占用过高

SELECT st.text, qp.query_plan, rq.* 
 FROM sys.dm_exec_requests RQ CROSS APPLY sys.dm_exec_sql_text(rq.sql_handle) as st 
 CROSS APPLY sys.dm_exec_query_plan(rq.plan_handle) as qp order by RQ.CPU_time desc 
 

  14.执行动态SQL脚本

DECLARE @str NVARCHAR(MAX)
DECLARE @index INT
SET @str=''
SET @index=1
WHILE(@index<=5)
BEGIN
    SET @str+='select '+CAST(@index AS NVARCHAR)+' AS ID '
    IF(@index<5)
    BEGIN
        SET @str+=' UNION ALL '
    END 
    SET @index=@index+1;
END 
PRINT(@str)
EXEC (@str)

   15.SQL 分组后取条件值 最大/最小 的数据行

--按 [No],[Name],[Spec] 分组后，取时间最大的 数据行
SELECT ROW_NUMBER() OVER(ORDER BY 辅材编码 ASC) AS rowno,AAA.* 
FROM ( SELECT 
      [PartInfoID] as 辅材样式ID
      ,[No] as 辅材编码
      ,[Name]as 辅材名称
      ,[Spec] as 辅材规格 
      ,[DbNum] as 库存前 
      ,[InNum] as 入库数 
      ,[OutNum]as 出库数
      ,[CurNum]as 库存后
      ,[WeightAvgPrice]as 加权平均价
      ,[UserName]as 操作人 
      ,CONVERT(varchar(100),Createtime, 20) AS 操作时间
      ,ROW_NUMBER() OVER(PARTITION BY [No],[Name],[Spec] ORDER BY Createtime DESC) as GroupNum
  FROM Biz_Stuff_PartInfo_Recode  
   where Createtime >='2022-07-01 16:46:02' and Createtime<'2022-07-04 16:46:02'
  ) AS AAA
  WHERE AAA.GroupNum=1

 16.查询表的某个字段的约束

select b.name from sysobjects b join syscolumns a on b.id = a.cdefault
where a.id = object_id('Biz_Sell_ProduceInstruction') AND a.name ='PordVolume' 

ALTER TABLE Biz_Sell_ProduceInstruction DROP CONSTRAINT XXX

ALTER TABLE Biz_Sell_ProduceInstruction drop COLUMN PordVolume

 17.删除字段同时删除该字段的约束

iF EXISTS ( SELECT  * FROM syscolumns WHERE id = OBJECT_ID('Biz_Audit_StuffMaterialSettle') AND name = 'IsSummary' )
BEGIN
    DECLARE @tablename VARCHAR(100), @columnname VARCHAR(100), @tab VARCHAR(100)
    SET @tablename='Biz_Audit_StuffMaterialSettle'--表名
    SET @columnname='IsSummary'--字段名
    DECLARE  @defname VARCHAR(100) 
    DECLARE  @cmd VARCHAR(100)
    SELECT @defname = name FROM sysobjects so 
    inner JOIN sysconstraints sc ON so.id = sc.constid
    WHERE object_name(so.parent_obj) = @tablename 
    AND so.xtype = 'D'
    AND sc.colid =(SELECT colid FROM syscolumns
    WHERE id = object_id(@tablename) AND name = @columnname)
    SET @cmd='alter table '+ @tablename+ ' drop constraint '+ @defname
    IF @cmd is null PRINT 'No default constraint to drop'
    EXEC (@cmd)
    
    ALTER TABLE dbo.Biz_Audit_StuffMaterialSettle DROP COLUMN IsSummary
END
GO

18