数据库常用SQL

查看表被哪些视图或存储过程引用
SELECT OBJECT_NAME(id) FROM syscomments
WHERE id IN(SELECT object_id FROM sys.objects WHERE type='P')
AND text LIKE '%TABLE%'

 --根据分隔符一条变多条

select * INTO #temp from (SELECT '1-2-3'运单号,'a' 发票号码)t;
WITH cte AS (select 发票号码,SUBSTRING(运单号,1,CHARINDEX('-',运单号+'-')-1) as 运单号,CHARINDEX('-',运单号+'-') as station from #temp
union all
select a.发票号码,SUBSTRING(a.运单号,b.station+1,CHARINDEX('-',a.运单号+'-',b.station+1)-b.station-1),CHARINDEX('-',a.运单号+'-',b.station+1)
FROM #temp AS a join cte AS b on a.发票号码=b.发票号码
where CHARINDEX('-',a.运单号+'-',b.station+1)>0)
select 发票号码,运单号 from cte
DROP TABLE #temp

--查询库所有表名
SELECT NAME from sysobjects where xtype='u'

--查询所有列名
SELECT columnname = a.name , IsPK = CASE WHEN EXISTS ( SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND parent_obj = a.id AND name IN ( SELECT name FROM sysindexes WHERE indid IN ( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid )))THEN 1 ELSE 0 END , columntype = b.name FROM syscolumns a LEFT JOIN systypes b ON a.xusertype = b.xusertype INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties' LEFT JOIN syscomments e ON a.cdefault = e.id WHERE d.name = '[表名]' ORDER BY a.id ,a.colorder

--修改表字段类型
ALTER TABLE [表名] ALTER COLUMN 字段名 NVARCHAR(50)
--新增字段
alter table [表名] add 字段名 int default 0
--删除字段
alter table [表名] drop 字段名

-- 批量更新列名
SELECT a.name ,' EXEC sp_rename ''MVKE.['+a.name+']'','''+'f'+CAST((CAST(REPLACE(a.name,'列 ','') as int)+1 ) as NVARCHAR(50))+''',''COLUMN'''
FROM syscolumns a
LEFT JOIN systypes b ON a.xusertype = b.xusertype
INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties'
LEFT JOIN syscomments e ON a.cdefault = e.id WHERE d.name = 'MVKE' ORDER BY a.id ,a.colorder

--重建索引
DECLARE @name varchar(100)
DECLARE authors_cursor CURSOR FOR
Select [name] from sysobjects where xtype='u' order by id
OPEN authors_cursor
FETCH NEXT FROM authors_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX (@name, '', 90)
FETCH NEXT FROM authors_cursor
INTO @name
END
deallocate authors_cursor


--查看被锁表：
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 = 93 --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid as varchar)
exec(@sql)


--捕获数据阻塞
use master
go
while 1=1
begin
print 'Start time:'+convert(varchar(26),getdate(),121)
print 'Running processes'
select spid,blocked,waittype,waittime,lastwaittype,waitresource,dbid,uid,cpu,physical_io,memusage,login_time,last_batch,
open_tran,status,hostname,program_name,cmd,net_library,loginame
from sysprocesses
where blocked<>0 and blocked<>spid
--where(ipid<>0) or(spid<51)
--Change it if you only want to see the working processes
print '*****lockinfo*****'
select convert(smallint,req_spid) as spid,
rsc_dbid as dbid,
rsc_objid as objid,
rsc_indid as indid,
substring(v.name,1,4) as type,
substring(rsc_text,1,16) as resource,
substring(u.name,1,8) as mode,
substring(x.name,1,5) as status
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
where master.dbo.syslockinfo.rsc_type=v.number
and v.type='LR'
and master.dbo.syslockinfo.req_status=x.number
and x.type='LS'
and master.dbo.syslockinfo.req_mode+1=u.number
and u.type='L'
and req_spid=93
order by spid

print 'inputbuffer for running processes'
declare @spid varchar(6)
declare ibuffer cursor fast_forward for
select cast(spid as varchar(6)) as spid from sysprocesses where spid=156 or spid=177
or spid=214 or spid=206
open ibuffer
fetch next from ibuffer into @spid
while (@@FETCH_STATUS!=-1)
begin
print ''
print 'DBCC inputbuffer for spid '+@spid
exec ('dbcc inputbuffer('+@spid+')')
fetch next from ibuffer into @spid
end
deallocate ibuffer
waitfor delay '0:0:10'
end

分隔返回数据行

CREATE FUNCTION [dbo].[f_Split](@c NVARCHAR(max),@split NVARCHAR(2))
returns @t table(colval NVARCHAR(max))
as
begin

while(charindex(@split,@c)<>0)
begin
insert @t(colval) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(colval) values ( @c)
return
END


GO