适用于SQL开发人员的7个便捷的SQL脚本

我们所做的许多事情都取决于我们拥有的知识。 如果我们知道可以做什么，那么只有这样我们才能做出更明智，更有效的决策。 这就是为什么口袋里准备好快速提示和技巧总是很好的原因。 该原则适用于所有地方，包括MS-SQL开发人员。

在本文中，我想分享一些SQL脚本，这些脚本对我作为SQL开发人员的日常工作非常有用。 我将提供一个简短的场景，说明在哪里可以将这些脚本与下面的脚本一起使用。

注意：在从这些脚本中受益之前，强烈建议先在测试环境中运行所有提供的脚本，然后再在实时数据库上运行它们以确保安全。

1.在所有SQL过程中搜索文本

我们能想象今天没有Control-F的生活吗？ 还是没有搜索引擎的生活！ 可怕的，不是吗？ 现在，假设您的数据库中有20-30个sql过程，您需要查找包含某个单词的过程。

绝对可以做到这一点的方法是一次打开每个过程，然后在过程内部进行Control-F。 但这是手动的，重复的和无聊的。 因此，这是一个快速脚本，可让您实现这一目标。

SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE m.definition Like '%search_text%'

2.比较具有相同架构的两个不同数据库的表中的行数

如果您有一个大型数据库，并且数据库的数据源是每天运行的某个ETL（提取，转换，加载）过程，那么下一个脚本适合您。

假设您有每天运行的脚本来将数据提取到数据库中，并且此过程每天大约需要五个小时。 随着您开始更深入地研究此过程，您会发现一些可以优化脚本以在不到四个小时的时间内完成任务的区域。

您想尝试这种优化，但是由于您已经在生产服务器上拥有了当前的实现，因此逻辑上的事情是在单独的数据库中尝试优化的过程，您可以使用现有数据库进行复制。

现在，一旦准备好，您将运行两个ETL流程并比较提取的数据。 如果您的数据库中有许多表，则此比较可能需要一段时间。 因此，这是一个促进此过程的快速脚本。

use YourDatabase_1
CREATE TABLE #counts
(
table_name varchar(255),
row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'

use YourDatabase_2
CREATE TABLE #counts_2
(
table_name varchar(255),
row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts_2 (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'

SELECT a.table_name,
a.row_count as [Counts from regular run],
b.row_count as [Counts from mod scripts],
a.row_count - b.row_count as [difference]
FROM #counts a
inner join #counts_2 b on a.table_name = b.table_name
where a.row_count <> b.row_count
ORDER BY a.table_name, a.row_count DESC

3.一次备份多个数据库

在任何IT公司中，新雇用的程序员（或sql开发人员）在编写其第一个SQL查询之前必须做的第一件事是购买生产数据库的工作版本的保险，即进行备份。

创建备份并使用备份版本的这一单一操作使您可以自由地执行和实践任何类型的数据转换，因为它确保即使破坏了公司客户的数据也可以将其恢复。 实际上，不仅是新员工，甚至来自同一IT公司的资深人士都不会在不创建备份的情况下执行任何数据转换。

尽管在SQL Server中备份数据库不是一项艰巨的任务，但它
肯定是耗时的，尤其是当您需要一次备份多个数据库时。 因此，下一个脚本对此非常方便。

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup directory
SET @path = 'E:\\Sovit\_BackupFolder\'
exec master.dbo.xp_create_subdir @path

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('DB_1','DB_2','DB_3',
'DB_4','DB_5','DB_6') -- only these databases

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name  
END

CLOSE db_cursor
DEALLOCATE db_cursor

4.一次收缩多个数据库日志

每个SQL Server数据库都有一个事务日志，该日志记录所有事务以及每个事务对数据库所做的修改。 事务日志是数据库的重要组成部分，如果发生系统故障，可能需要事务日志才能使数据库恢复一致状态。

然而，随着交易数量开始增加，空间可用性开始成为主要问题。 幸运的是，SQL Server允许您通过减少事务日志的大小来回收多余的空间。

虽然您可以手动收缩日志文件，但是一次使用提供的UI一次，谁有时间手动收缩日志文件？ 以下脚本可用于快速收缩多个数据库日志文件。

DECLARE @logName as nvarchar(50)
DECLARE @databaseID as int

DECLARE db_cursor CURSOR FOR 
SELECT TOP 10 name,database_id -- only 10 but you can choose any number
FROM sys.master_Files WHERE physical_name like '%.ldf'
and physical_name not like 'C:\%'  -- specify your database paths
and name not in ('mastlog') -- any database logs that you would like to exclude
ORDER BY size DESC

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @logName , @databaseID

WHILE @@FETCH_STATUS = 0 
BEGIN 
    DECLARE @databaseName as nvarchar(50)
 SET @databaseName =  DB_NAME(@databaseID)

  DECLARE @tsql nvarchar(300)
 SET @tsql='USE ['+@databaseName+'] ALTER DATABASE ['+@databaseName+'] set recovery simple DBCC SHRINKFILE ('+@logName+' , 1)'
 EXEC(@tsql)

    FETCH NEXT FROM db_cursor INTO @logName , @databaseID
END 
CLOSE db_cursor
DEALLOCATE db_cursor

5.通过设置单用户模式来限制与数据库的连接

单用户模式指定一次只能有一个用户可以访问该数据库，通常用于维护操作。 基本上，如果在将数据库设置为单用户模式时其他用户已连接到数据库，则他们与数据库的连接将关闭，而不会发出警告。

在需要从特定时间点将数据库还原到版本或需要防止任何其他进程访问数据库的可能更改的情况下，这非常有用。

USE master;
GO
ALTER DATABASE YourDatabaseName
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE YourDatabaseName
SET READ_ONLY;
GO
ALTER DATABASE YourDatabaseName
SET MULTI_USER;
GO

6. SQL中的字符串函数以生成动态文本

许多编程语言都允许您在字符串文本中插入值，这在生成动态字符串文本时非常有用。 由于SQL默认情况下不提供任何此类功能，因此这是一种快速的补救方法。 使用以下功能，可以在字符串文本中动态插入任意数量的文本。

--Example Usage
--declare @test varchar(400)
--select @test = [dbo].[FN_SPRINTF] ('I am %s and you are %s', '1,0', ',') --param separator ','
--print @test -- result: I am 1 and you are 0
--select @test = [dbo].[FN_SPRINTF] ('I am %s and you are %s', '1#0', '#') --param separator ','
--print @test -- result: I am 1 and you are 0

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- AUTHOR: <SOVIT POUDEL>
-- =============================================
CREATE FUNCTION DBO.FN_SPRINTF
(
@STRING VARCHAR(MAX),
@PARAMS VARCHAR(MAX),
@PARAM_SEPARATOR CHAR(1) = ','
)
RETURNS VARCHAR(MAX)
AS
BEGIN

DECLARE @P VARCHAR(MAX)
DECLARE @PARAM_LEN INT

SET @PARAMS = @PARAMS + @PARAM_SEPARATOR   
SET @PARAM_LEN = LEN(@PARAMS)
WHILE NOT @PARAMS = ''
BEGIN
    SET @P = LEFT(@PARAMS+@PARAM_SEPARATOR, CHARINDEX(@PARAM_SEPARATOR, @PARAMS)-1)       
    SET @STRING = STUFF(@STRING, CHARINDEX('%S', @STRING), 2, @P)
    SET @PARAMS = SUBSTRING(@PARAMS, LEN(@P)+2, @PARAM_LEN)
END
RETURN @STRING

END

7.打印表列定义

比较具有相似架构的多个数据库时，必须查看表列的详细信息。 列的定义（数据类型，可为null？）与列本身的名称一样重要。

现在，对于具有许多表的数据库和具有许多列的表，可能需要花费一些时间才能将每个列与另一个数据库的另一个表中的列进行手动比较。 下一个脚本可以精确地用于自动化该过程，因为它可以打印给定数据库的所有表的定义。

SELECT
sh.name+'.'+o.name AS ObjectName,
s.name as ColumnName
,CASE
    WHEN t.name IN ('char','varchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length) END+')'
	WHEN t.name IN ('nvarchar','nchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length/2) END+')'
	WHEN t.name IN ('numeric') THEN t.name+'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')'
	ELSE t.name
END AS DataType
,CASE
	 WHEN s.is_nullable=1 THEN 'NULL'
	ELSE 'NOT NULL'
END AS Nullable       
	
FROM sys.columns s
INNER JOIN sys.types t ON s.system_type_id=t.user_type_id and t.is_user_defined=0
INNER JOIN sys.objects o ON s.object_id=o.object_id
INNER JOIN sys.schemas sh on o.schema_id=sh.schema_id

WHERE O.name IN
   (select table_name from information_schema.tables) 

ORDER BY sh.name+'.'+o.name,s.column_id

结论

在本文中，我们研究了七个有用的脚本，这些脚本可以减少大量繁琐的手动工作，并提高SQL开发人员的整体效率。 我们还研究了可以实现这些脚本的不同方案。

如果您正在寻找更多的SQL脚本来研究（或使用），请毫不犹豫地查看我们在CodeCanyon上提供的功能 。

一旦您掌握了这些脚本的作用，您肯定会开始确定许多其他可以有效使用这些脚本的方案。

祝好运！

翻译自: https://code.tutsplus.com/tutorials/7-handy-sql-scripts-for-sql-developers--cms-25834