自己积累的一些SQL语句

--查询外键select   oSub.name  AS  [子表名称],  fk.name AS  [外键名称],  SubCol.name AS [子表列名],  oMain.name  AS  [主表名称],  MainCol.name AS [主表列名]from   sys.foreign_keys fk  JOIN sys.all_objects oSub      ON (fk.parent_object_id = oSub.object_id)JOIN sys.all_objects oMain     ON (fk.referenced_object_id = oMain.object_id)JOIN sys.foreign_key_columns fkCols     ON (fk.object_id = fkCols.constraint_object_id)JOIN sys.columns SubCol     ON (oSub.object_id = SubCol.object_id          AND fkCols.parent_column_id = SubCol.column_id)JOIN sys.columns MainCol     ON (oMain.object_id = MainCol.object_id  AND fkCols.referenced_column_id = MainCol.column_id)                        --查找BAK表缺少的列select name from sys.columnswhere object_id = object_id(N'dwCaseDrAdvices')--修改表名and name not in (select name from sys.columnswhere object_id = object_id(N'dwCaseDrAdvices_BAK'))--修改BAK表名--查找两个表列类型不一致的列select a.name from (select * from sys.columnswhere object_id = object_id(N'dwCaseDrAdvices')) a --修改表名join (select * from sys.columnswhere object_id = object_id(N'dwCaseDrAdvices_BAK')) b--修改BAK表名on a.name = b.namewhere a.user_type_id != b.user_type_idor a.max_length != b.max_lengthor a.system_type_id != b.system_type_id--修改表中的列类型alter table dwCaseDrAdvices--需要修改的表名alter column DradviceName --需要修改的列名int --需要修改成的类型--根据条件查出结果插入到另一张表中select * into #mytable from CIS2014..HRCaseHisRecord where iDiagnoseId = 50124 and iCaseHisId = 385765insert into doctorcis2013..HRCaseHisRecordselect *  From #mytable--RowNumberselect orderid,custid,orderdate,ROW_NUMBER() over(partition by custidorder by orderdate) as rownum from Sales.Orders/*orderidcustidorderdaterownum1064312007-08-25 00:00:00.00011069212007-10-03 00:00:00.00021070212007-10-13 00:00:00.00031083512008-01-15 00:00:00.00041095212008-03-16 00:00:00.00051101112008-04-09 00:00:00.00061030822006-09-18 00:00:00.00011062522007-08-08 00:00:00.00021075922007-11-28 00:00:00.00031092622008-03-04 00:00:00.0004*/--查看被锁表：select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName   from   sys.dm_tran_locks where resource_type='OBJECT' --spid   锁表进程 --tableName   被锁表名 --解锁： declare @spid  int Set @spid  = 57 --锁表进程declare @sql varchar(1000)set @sql='kill '+cast(@spid  as varchar)exec(@sql)--判断一个表是否存在--存储过程 Pif(OBJECT_ID('table_name','U')) is not nullbeginendelsebeginend--判断列是否存在if COL_LENGTH('pubStyleList','nShortCutKey')) is nullbeginalter table pubStyleListadd nShortCutKey nvarchar(10)end/*增加约束*/示例：--添加主键约束alter table stuInfoadd constraint PK_stuNo primary key(stuNo)--添加唯一键约束alter table stuInfoadd constraint UQ_stuID unique(stuID)--添加默认约束alter table stuInfoadd constraint DF_stuAddress default('地址不详') for stuAddress--添加检查约束alter table stuInfoadd constraint CK_stuAge check(stuAge between 15 and 40)--添加外键约束alter table stuInfoadd constraint FK_stuNo foreign key(stuNo) references stuInfo(stuNo)--取最近整数select CEILING(1.08)  /*删除约束*/ alter table 表名 drop constraint 约束名--当 IDENTITY_INSERT 设置为 OFF 时，不能为表中的标识列插入显式值--允许将显式值插入表的标识列中 ON-允许  OFF-不允许set identity_insert OrderList ON--打开insert into OrderList(id,ordername,createdate)values(4520,'set',getdate())set identity_insert OrderList OFF--关闭--SQL 链接到Oracle服务器语句/*查询操作*/SELECT * FROM OPENQUERY(Province, 'select * from t_base_duty')/*删除操作*/DELETE FROM OPENQUERY(Province, 'select * from t_base_duty')/*插入操作*/INSERT INTO OPENQUERY (province,'SELECT * FROM t_base_role') (role_id,role_name,bureautype_id,is_system,b_use,field_order,b_del,identity_id) VALUES (10,'科比',7,1,0,1,0,2)/*更新操作*/UPDATE  OPENQUERY (province,'SELECT * FROM t_base_role') SET role_name='科比' WHERE role_id=10--创建连接服务器exec sp_addlinkedserver  'CisZy','','SQLOLEDB','111.111.111.200'exec sp_addlinkedsrvlogin 'CisZy','false',null,'SA',''--重新生秘钥--在本地服务器上执行:tempdb库中ALTER SERVICE MASTER KEY FORCE REGENERATE--比较两张表的结构select a.name as '列名',c.name as '数据类型',a.max_length as '长度',d.name as '数据类型',b.max_length as '长度' from (select * from sys.columnswhere object_id = object_id(N'HROPSList')) a --修改表名join (select * from sys.columnswhere object_id = object_id(N'HROPSList_bak')) b--修改BAK表名on a.name = b.namejoin sys.types c  on a.system_type_id =c.system_type_id join sys.types d  on a.system_type_id =d.system_type_id where a.user_type_id != b.user_type_idor a.max_length != b.max_lengthor a.system_type_id != b.system_type_id--清除表 truncate  table hradvicerunzy--OPENDATASOURCE--开启配置EXECUTE sp_configure 'show advanced options',1RECONFIGUREEXECUTE sp_configure 'Ad Hoc Distributed Queries',1RECONFIGURESELECT   top 10 *FROM      OPENDATASOURCE(         'SQLOLEDB',         'Data Source=globe01;User ID=xw_cai;Password=123'         ).[order].dbo.ordermaster--查询外键所在表select oSub.name  AS  [子表名称],fk.name AS  [外键名称],SubCol.name AS [子表列名],oMain.name  AS  [主表名称],MainCol.name AS [主表列名]from sys.foreign_keys fk  JOIN sys.all_objects oSub  ON (fk.parent_object_id = oSub.object_id)JOIN sys.all_objects oMain ON (fk.referenced_object_id = oMain.object_id)JOIN sys.foreign_key_columns fkCols ON (fk.object_id = fkCols.constraint_object_id)JOIN sys.columns SubCol ON (oSub.object_id = SubCol.object_id  AND fkCols.parent_column_id = SubCol.column_id)JOIN sys.columns MainCol ON (oMain.object_id = MainCol.object_id  AND fkCols.referenced_column_id = MainCol.column_id)where fk.name = 'FK_HRDIAGLI_REFERENCE_HRFIRSTP1'--一行数据拆分成两行数据--cOPSName: 右腋下淋巴清扫术+子宫下段剖宫产declare @SplitChar varchar(2)set @SplitChar = '+'select top 5000 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns bSelect a.iOPSList,COl2=substring(a.cOPSName,b.ID,charindex(@SplitChar,a.cOPSName+@SplitChar,b.ID)-b.ID) from HROPSList a,#Num bwhere  a.iOPSList = 74222 and charindex(@SplitChar,@SplitChar+a.cOPSName,b.ID)=b.IDdrop table #Num--SQL生成脚本SELECT 'INSERT INTO pubDictStaff(No_Staff,cStaffCode,cName) VALUES('+cast(No_Staff as varchar(10))+',''' +CONVERT(varchar(19), cStaffCode, 121) + ''',''' + cName + ''')' FROM pubDictStaff --替换函数返回结果Li2select REPLACE('LiChao','Chao','2')--查询数据库中所有表的行数select b.name as tablename ,c.row_count as datacountfrom sys.indexes a ,sys.objects b ,sys.dm_db_partition_stats cwhere a.[object_id] = b.[object_id]AND b.[object_id] = c.[object_id]AND a.index_id = c.index_idAND a.index_id < 2AND b.is_ms_shipped = 0and c.row_count >0--查询没有分配班级的学生信息select * from student where not exists (select * from classes where id = student.cid);--查询主键SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='pubDictStaff'--Sql Server数据库用SQL语句查询方法如下：select name from sysobjects where xtype='TR' --所有触发器select name from sysobjects where xtype='P' --所有存储过程select name from sysobjects where xtype='V' --所有视图select name from sysobjects where xtype='U' --所有表--查询表初特定列外所有的列declare @col nvarchar(max)set @col=''select @col=@col+','+name from syscolumns where id=object_id('HRDiagnose') and name<>'iNurseLevel' order by colid--禁用：ALTER TABLE trig_example DISABLE TRIGGER trig1GO--恢复：ALTER TABLE trig_example ENABLE TRIGGER trig1GO--禁用某个表上的所有触发器ALTER TABLE 你的表 DISABLE TRIGGER all--启用某个表上的所有触发器ALTER TABLE 你的表 enable TRIGGER all--高效去重WITH TEST AS  (    SELECT ROW_NUMBER()     OVER(PARTITION BY Column1,Column2,Column3 ORDER BY ID )     AS NUM,* FROM TableName ) DELETE FROM TEST WHERE NUM != 1--删除重复语句 SELECT  * FROM  frmZHProductResult  where  id<>(select max(id) from frmZHProductResult d where frmZHProductResult.zhproductid =d.zhproductid )  AND zhproductid in (select zhproductid from frmZHProductResult b GROUP BY ZhproductId HAVING COUNT(ZhproductId)>1)  --比较两张表中新增数据select * From czszyyhis..DictStaff  awhere not exists (select * from DictStaff b where a.No_Staff = b.No_Staff)--重命名表明EXEC sp_rename 'DictStaff','DictStaff_Table'--多行数据合并成一行select No_Dept,[EnableString]=stuff((select ','+ CAST(iAdviceItemId as varchar(10)) from pubDeptAdviceItem where No_Dept=a.No_Dept  for xml path('')),1,1,'')from pubDeptAdviceItem a group by No_Dept--查看小数点长度大于3select iRetail,* From DictDrugactwhere iRetail*100>floor(100*iRetail)--查看表是否存在标识列if exists(select * from syscolumns where id=object_id(N'DictStaff') and status=0x80)print '存在'elseprint '不存在'--分页取数据SELECT ROW_NUMBER() OVER (ORDER BY No_Staff) AS RowNumber,* into #tempFROM DictStaff with(nolock) select top 100 * from #temp where RowNumber > 10 * ({0} - 1)drop table #temp--查询包含连接服务器的视图、存储过程select distinct b.namefrom dbo.syscomments a, dbo.sysobjects bwhere a.id=b.id  and b.xtype in ('p','v') and a.text like '%text%'order by name--查询表的行数SELECT a.name, b.rows as '行数'FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.idWHERE (a.type = 'u') AND (b.indid IN (0, 1))ORDER BY b.rows DESC