常用 SQL脚本 收集

declare @t table(成绩 int,学生  varchar(10))
insert @t select
100,'小张' union select  
70     ,'小力' union select  
80     ,'小刚' union select  
78     ,'小王'


select *,Pid = (select count(1) from @t where 成绩 > a.成绩)+1
from @t a
order by pid


/*
成绩          学生         Pid         
----------- ---------- ----------- 
100         小张         1
80          小刚         2
78          小王         3
70          小力         4

（所影响的行数为 4 行）

*/

select *,dense_rank()over(order by IntegralTotal desc) from HT_Members

-- 1.SQL Server查询表的主外键
SELECT 
外键表ID   = b.fkeyid ,
外键表名称 = object_name(b.fkeyid) ,
外键列ID   = b.fkey ,
外键列名   = (SELECT name FROM syscolumns WHERE colid = b.fkey AND id = b.fkeyid) ,
主键表ID   = b.rkeyid ,
主键表名   = object_name(b.rkeyid) ,
主键列ID   = b.rkey ,
主键列名   = (SELECT name FROM syscolumns WHERE colid = b.rkey AND id = b.rkeyid) ,
级联更新   = ObjectProperty(a.id,'CnstIsUpdateCascade') ,
级联删除   = ObjectProperty(a.id,'CnstIsDeleteCascade') 
FROM sysobjects a 
JOIN sysforeignkeys b ON a.id = b.constid 
JOIN sysobjects c ON a.parent_obj = c.id 
WHERE a.xtype = 'F' AND c.xtype = 'U'
-- 2.sql 语句查找外键 以及对应的外键表名、列名和引用的主表名、列名
;WITH  CTE
        AS (
            SELECT  OBJECT_NAME(constraint_object_id) Constraint_Name , OBJECT_NAME(parent_object_id) Table_Name , C.name Column_Name
            FROM    sys.foreign_key_columns FK
            INNER JOIN sys.columns C
            ON      FK.parent_object_id = C.object_id
                    AND FK.parent_column_id = C.column_id
           )
  SELECT  C.Constraint_Name , C.Table_Name , C.Column_Name , OBJECT_NAME(FK.referenced_object_id) Referenced_Table_Name , SC.name Referenced_Column_Name
  FROM    CTE C
  INNER JOIN sys.foreign_key_columns FK
  ON      C.Constraint_Name = OBJECT_NAME(FK.constraint_object_id)
  INNER JOIN sys.columns SC
  ON      FK.referenced_object_id = SC.object_id
          AND FK.referenced_column_id = SC.column_id