Microsoft SQL Server 代码片段收集

MS SQL 代码片段收集

查询依赖关系

SELECT DISTINCT object_Name(id) FROM syscomments
WHERE id IN (SELECT id FROM sysobjects WHERE type IN ('P'))
AND text LIKE '%table_name%'

查询包含此表的语句

select definition from sys.sql_modules where definition like '%table_name%'

查看索引

SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('table_name', N'U')

查看约束

sp_helpconstraint 'table_name'

删除约束

alter table [table_name] drop constraint [constraint_name]

/*  通过存储过程查询一个表的所有索引
sp_helpindex [ @objname = ] 'name'
参数 :[@objname =] 'name'  是当前数据库中表或视图的名称。name 的数据类型为 nvarchar(776)，没有默认值。 */
sp_helpindex 'table_name'

查询系统表中的索引及索引列

SELECT  indexname = a.name , tablename = c. name , indexcolumns = d.name , a.indid
FROM  sysindexes a JOIN sysindexkeys b ON a.id = b.id  AND a.indid = b.indid
  JOIN sysobjects c ON b.id = c.id
  JOIN syscolumns d ON b.id = d.id  AND b.colid = d.colid
WHERE  a.indid NOT IN ( 0 , 255 )
and  c.xtype='U' AND c.name = 'table_name' and  c.status>0
ORDER BY c. name ,a.name ,d.name

删除索引

DROP INDEX [index_name] ON [table_name]

查看统计信息

sp_autostats 'table_name'

删除统计信息

DROP STATISTICS [index_name]

只取数字

SELECT CASE WHEN patIndex('%[^0-9]%',@S)>0 THEN left(@S,patIndex('%[^0-9]%',@S)-1) ELSE 0 END

跨库导数据

exec sp_configure 'Ad Hoc Distributed Queries' , [0 or 1]  --用时开启，用完关闭
reconfigure
exec sp_configure 'show advanced options' , [0 or 1]
reconfigure SELECT * FROM  openrowset  ( 'SQLOLEDB' , '[servername]' ; '[username]' ; '[password]' , [databasename].[dbo].[tablename] )

数据库全角字符转半角

CREATE FUNCTION f_Convert
(
  @str NVARCHAR(4000), --要转换的字符串
  @flag bit --转换标志,0转换成半角,1转换成全角
)RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @pat nvarchar(8),@step int,@i int,@spc int
IF @flag=0
SELECT @pat=N'%[！-～]%',@step=-65248,
@str=REPLACE(@str,N'　 ',N' ')
ELSE
SELECT @pat=N'%[!-~]%',@step=65248,
@str=REPLACE(@str,N' ',N'　 ')
SET @i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str)
WHILE @i> 0
SELECT @str=REPLACE(@str,
SUBSTRING(@str,@i,1),
NCHAR(UNICODE(SUBSTRING(@str,@i,1))+@step))
,@i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str)
RETURN(@str)
END
GO
//调用：
UPDATE [tablename] set brnl=dbo.f_Convert(brnl,0)

实现 随机查询 （over函数和newid）

sql 中的 over 函数和 row_numbert() 函数配合使用，可生成行号。可对某一列的值进行排序，对于相同值的数据行进行分组排序。
newid（）创建 uniqueidentifier 类型的唯一值，类似这样的样式 6F9619FF-8B86-D011-B42D-00C04FC964FF
由于select每当扫描得到一条记录时候均调用该函数，这样产生每条结果记录的各不相同值，如果按照此值排序（ order by NEWID () ），就类似于产生了随机记录的效果。

select row_number() over(order by AID DESC) as rowid,* from bb

根据给定时间范围生成连续日期

DECLARE @StartDate DateTime;
DECLARE @EndDate DateTime;
SET @StartDate='2020-01-01';
SET @EndDate='2020-01-07';

SELECT DateAdd(day,number,@StartDate)
FROM master..spt_values
WHERE type = 'p'
AND number <= DateDiff(day,@StartDate,@EndDate)

删除数据库中所有表

declare @tname varchar(8000)
set @tname=''
select @tname=@tname + Name + ',' from sysobjects where xtype='U'
select @tname='drop table ' + left(@tname,len(@tname)-1)
exec(@tname)

去掉小数点后面多余的0

CREATE FUNCTION dbo.fun_clear_zero (@inValue varchar(50))
RETURNS varchar(50)
AS
BEGIN
  DECLARE @returnValue varchar(20);
  IF (@inValue = '')
    SET @returnValue = ''; --空的时候为空
  ELSE IF (CHARINDEX('.', @inValue) = '0')
    SET @returnValue = @inValue; --针对不含小数点的
  ELSE IF (SUBSTRING(REVERSE(@inValue), PATINDEX('%[^0]%', REVERSE(@inValue)), 1) = '.')
    SET @returnValue = LEFT(@inValue, LEN(@inValue) - PATINDEX('%[^0]%', REVERSE(@inValue))); --针对小数点后全是0的
  ELSE
    SET @returnValue = LEFT(@inValue, LEN(@inValue) - PATINDEX('%[^0]%.%', REVERSE(@inValue)) + 1); --其他任何情形
  RETURN @returnValue;
END;
GO

SQL 多关键字查询并根据匹配程度排序

--创建测试表
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Score]') AND type in (N'U'))
DROP TABLE [dbo].[Score]
GO
CREATE TABLE [dbo].[Score](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](50) NULL,
    [Subject] [nvarchar](50) NULL,
    [Source] [numeric](18, 0) NULL
) ON [PRIMARY]
GO

--插入测试数据
INSERT INTO [Score] ([UserName],[Subject],[Source])
    SELECT N'张三',N'语文',60  UNION ALL
    SELECT N'李四',N'数学',70  UNION ALL
    SELECT N'王五',N'英语',80  UNION ALL
    SELECT N'王五',N'数学',75  UNION ALL
    SELECT N'王五',N'语文',57  UNION ALL
    SELECT N'李四',N'语文',80  UNION ALL
    SELECT N'张三',N'英语',100
GO

--入参：
DECLARE @UserName VARCHAR(100)
DECLARE @Subject VARCHAR(100)
DECLARE @Source VARCHAR(100)
SET @UserName='张'
SET @Subject='语文'
SET @Source='70'


SELECT id, sum((CASE WHEN charIndex(@UserName,[UserName])>0 THEN 1 ELSE 0 END)
              +(CASE WHEN charIndex(@Subject, [Subject])>0 THEN 1 ELSE 0 END)
              +(CASE WHEN [Source]>@Source THEN 1 ELSE 0 END)
              ) AS cnt
INTO #temp
FROM Score GROUP BY id


SELECT * FROM  Score a JOIN #temp b ON a.id=b.id ORDER BY b.cnt DESC,UserName
DROP TABLE #temp