自己积累的一些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.columns
where object_id = object_id(N'dwCaseDrAdvices')--修改表名
and name not in 
(select name from sys.columns
where object_id = object_id(N'dwCaseDrAdvices_BAK'))--修改BAK表名

--查找两个表列类型不一致的列
select a.name from 
(select * from sys.columns
where object_id = object_id(N'dwCaseDrAdvices')) a --修改表名
join (select * from sys.columns
where object_id = object_id(N'dwCaseDrAdvices_BAK')) b--修改BAK表名
on a.name = b.name
where a.user_type_id != b.user_type_id
or a.max_length != b.max_length
or 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 = 385765

insert into doctorcis2013..HRCaseHisRecord
select *  From #mytable

--RowNumber
select orderid,custid,orderdate,
ROW_NUMBER() over(partition by custid
order by orderdate) as rownum
 from Sales.Orders
/*
orderid	custid	orderdate	rownum
10643	1	2007-08-25 00:00:00.000	1
10692	1	2007-10-03 00:00:00.000	2
10702	1	2007-10-13 00:00:00.000	3
10835	1	2008-01-15 00:00:00.000	4
10952	1	2008-03-16 00:00:00.000	5
11011	1	2008-04-09 00:00:00.000	6
10308	2	2006-09-18 00:00:00.000	1
10625	2	2007-08-08 00:00:00.000	2
10759	2	2007-11-28 00:00:00.000	3
10926	2	2008-03-04 00:00:00.000	4
*/


--查看被锁表：

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)

--判断一个表是否存在
--存储过程 P
if(OBJECT_ID('table_name','U')) is not null
	begin
	end
else
	begin
	end
	
--判断列是否存在
if COL_LENGTH('pubStyleList','nShortCutKey')) is null
begin
alter table pubStyleList
add nShortCutKey nvarchar(10)
end

/*增加约束*/
示例：
--添加主键约束
alter table stuInfo
add constraint PK_stuNo primary key(stuNo)
--添加唯一键约束
alter table stuInfo
add constraint UQ_stuID unique(stuID)
--添加默认约束
alter table stuInfo
add constraint DF_stuAddress default('地址不详') for stuAddress
--添加检查约束
alter table stuInfo
add constraint CK_stuAge check(stuAge between 15 and 40)
--添加外键约束
alter table stuInfo
add 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.columns
where object_id = object_id(N'HROPSList')) a --修改表名
join (select * from sys.columns
where object_id = object_id(N'HROPSList_bak')) b--修改BAK表名
on a.name = b.name
join 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_id
or a.max_length != b.max_length
or a.system_type_id != b.system_type_id

--清除表 
truncate  table hradvicerunzy


--OPENDATASOURCE
--开启配置
EXECUTE sp_configure 'show advanced options',1
RECONFIGURE
EXECUTE sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE


SELECT   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 b

Select 
a.iOPSList,COl2=substring(a.cOPSName,b.ID,charindex(@SplitChar,a.cOPSName+@SplitChar,b.ID)-b.ID) 
from 
HROPSList a,#Num b
where  
a.iOPSList = 74222 
and charindex(@SplitChar,@SplitChar+a.cOPSName,b.ID)=b.ID

drop 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 

--替换函数返回结果Li2
select REPLACE('LiChao','Chao','2')

--查询数据库中所有表的行数
select b.name as tablename ,
c.row_count as datacount
from sys.indexes a ,
sys.objects b ,
sys.dm_db_partition_stats c
where a.[object_id] = b.[object_id]
AND b.[object_id] = c.[object_id]
AND a.index_id = c.index_id
AND a.index_id < 2
AND b.is_ms_shipped = 0
and 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 trig1
GO
--恢复：
ALTER TABLE trig_example ENABLE TRIGGER trig1
GO

--禁用某个表上的所有触发器
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  a
where 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


--查看小数点长度大于3
select 
iRetail,* From DictDrugact
where iRetail*100>floor(100*iRetail)

--查看表是否存在标识列
if exists(select * from syscolumns where id=object_id(N'DictStaff') and status=0x80)
print '存在'
else
print '不存在'

--分页取数据
SELECT ROW_NUMBER() OVER (ORDER BY No_Staff) AS RowNumber,* into #temp
FROM DictStaff with(nolock) 
select top 100 * from #temp where RowNumber > 10 * ({0} - 1)
drop table #temp

--查询包含连接服务器的视图、存储过程
select distinct b.name
from dbo.syscomments a, dbo.sysobjects b
where 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.id
WHERE (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY b.rows DESC