Advertisement
  1. Code
  2. Coding Fundamentals
  3. Databases & SQL

7 Handy SQL Scripts for SQL Developers

Scroll to top

A lot of things that we do depend upon the knowledge that we possess. If we are aware of what can be done, only then we can make smarter and more effective decisions. That is why it is always good to have quick tips and tricks handy in your pocket. This principle applies everywhere, including for MS-SQL developers. 

Through this article I would like to share a few SQL scripts which have proven to be very useful for my daily job as a SQL developer. I'll present a brief scenario about where each of these scripts can be used along with the scripts below.

Note: Before reaping the benefits from these scripts, it is highly recommended that all of the provided scripts be run in a test environment first before running them on a real-time database to ensure safety.

1. Search for Text Inside All the SQL Procedures

Can we imagine life without Control-F in today's world? Or a life without search engines! Dreadful, isn't it? Now imagine you have 20-30 sql procedures in your database and you need to find the procedure that contains a certain word. 

Definitely one way to do it is by opening each procedure one at a time and doing a Control-F inside the procedure. But this is manual, repetitive, and boring. So, here is a quick script that allows you to achieve this.

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

2. Compare Row Counts in Tables From Two Different Databases With the Same Schema

If you have a large database and the source of data for your database is some ETL (extract, transform, load) process that runs on a daily basis, this next script is for you. 

Say you have scripts that run on a daily basis to extract data into your database and this process takes about five hours each day. As you begin to look more deeply into this process, you find some areas where you can optimize the script to finish the task in under four hours. 

You would like to try out this optimization, but since you already have the current implementation on a production server, the logical thing to do is try out the optimized process in a separate database, which you would replicate using the existing database. 

Now, once ready, you would run both ETL processes and compare the extracted data. If you have a database with many tables, this comparison can take quite a while. So, here's a quick script that facilitates this process.

1
use YourDatabase_1
2
CREATE TABLE #counts
3
(
4
table_name varchar(255),
5
row_count int
6
)
7
8
EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
9
10
use YourDatabase_2
11
CREATE TABLE #counts_2
12
(
13
table_name varchar(255),
14
row_count int
15
)
16
17
EXEC sp_MSForEachTable @command1='INSERT #counts_2 (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
18
19
SELECT a.table_name,
20
a.row_count as [Counts from regular run],
21
b.row_count as [Counts from mod scripts],
22
a.row_count - b.row_count as [difference]
23
FROM #counts a
24
inner join #counts_2 b on a.table_name = b.table_name
25
where a.row_count <> b.row_count
26
ORDER BY a.table_name, a.row_count DESC

3. Back Up Multiple Databases at Once

In any IT company, the first thing a newly hired programmer (or sql developer) has to do before writing his or her first SQL query is buy insurance of the working version of the production database, i.e. make a backup.

This single act of creating a backup and working with the backup version gives you the freedom to perform and practice any kind of data transformation, as it ensures that even if you blow off the company's client's data, it can be recovered. In fact, not just new hires but even the veterans from the same IT company never perform any data transformation without creating backups. 

Although backing up databases in SQL Server is not a difficult task, it
definitely is time-consuming, especially when you need to back up many databases at once. So the next script is quite handy for this purpose.

1
DECLARE @name VARCHAR(50) -- database name

2
DECLARE @path VARCHAR(256) -- path for backup files

3
DECLARE @fileName VARCHAR(256) -- filename for backup

4
DECLARE @fileDate VARCHAR(20) -- used for file name

5
6
-- specify database backup directory

7
SET @path = 'E:\\Sovit\_BackupFolder\'

8
exec master.dbo.xp_create_subdir @path

9


10
-- specify filename format

11
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

12


13
DECLARE db_cursor CURSOR FOR

14
SELECT name

15
FROM master.dbo.sysdatabases

16
WHERE name IN ('DB_1','DB_2','DB_3',

17
'DB_4','DB_5','DB_6') -- only these databases

18


19
OPEN db_cursor

20
FETCH NEXT FROM db_cursor INTO @name

21


22
WHILE @@FETCH_STATUS = 0

23
BEGIN

24
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

25
BACKUP DATABASE @name TO DISK = @fileName

26


27
FETCH NEXT FROM db_cursor INTO @name  

28
END

29


30
CLOSE db_cursor

31
DEALLOCATE db_cursor

4. Shrink Multiple Database Logs at Once

Every SQL Server database has a transaction log that records all transactions and the database modifications made by each transaction. The transaction log is a critical component of the database and, if there is a system failure, the transaction log might be required to bring your database back to a consistent state. 

As the number of transactions starts increasing, however, space availability starts becoming a major concern. Fortunately, SQL Server allows you to reclaim the excess space by reducing the size of the transaction log.

While you can shrink log files manually, one at a time using the UI provided, who has the time to do this manually? The following script can be used to shrink multiple database log files rapidly.

1
DECLARE @logName as nvarchar(50)
2
DECLARE @databaseID as int
3
4
DECLARE db_cursor CURSOR FOR 
5
SELECT TOP 10 name,database_id -- only 10 but you can choose any number

6
FROM sys.master_Files WHERE physical_name like '%.ldf'
7
and physical_name not like 'C:\%'  -- specify your database paths

8
and name not in ('mastlog') -- any database logs that you would like to exclude

9
ORDER BY size DESC
10
11
OPEN db_cursor 
12
FETCH NEXT FROM db_cursor INTO @logName , @databaseID
13
14
WHILE @@FETCH_STATUS = 0 
15
BEGIN 
16
    DECLARE @databaseName as nvarchar(50)
17
 SET @databaseName =  DB_NAME(@databaseID)
18
19
  DECLARE @tsql nvarchar(300)
20
 SET @tsql='USE ['+@databaseName+'] ALTER DATABASE ['+@databaseName+'] set recovery simple DBCC SHRINKFILE ('+@logName+' , 1)'
21
 EXEC(@tsql)
22
23
    FETCH NEXT FROM db_cursor INTO @logName , @databaseID
24
END 
25
CLOSE db_cursor
26
DEALLOCATE db_cursor

5. Restrict Connection to the Database by Setting Single-User Mode

Single-user mode specifies that only one user at a time can access the database and is generally used for maintenance actions. Basically, if other users are connected to the database at the time that you set the database to single-user mode, their connections to the database will be closed without warning. 

This is quite useful in the scenarios where you need to restore your database to the version from a certain point in time or you need to prevent possible changes by any other processes accessing the database.

1
USE master;
2
GO
3
ALTER DATABASE YourDatabaseName
4
SET SINGLE_USER
5
WITH ROLLBACK IMMEDIATE;
6
GO
7
ALTER DATABASE YourDatabaseName
8
SET READ_ONLY;
9
GO
10
ALTER DATABASE YourDatabaseName
11
SET MULTI_USER;
12
GO

6. String Function in SQL to Generate Dynamic Texts

Many programming languages allow you to insert values inside string texts, which is very useful when generating dynamic string texts. Since SQL doesn't provide any such function by default, here is a quick remedy for that. Using the function below, any number of texts can be dynamically inserted inside string texts.

1
--Example Usage

2
--declare @test varchar(400)

3
--select @test = [dbo].[FN_SPRINTF] ('I am %s and you are %s', '1,0', ',') --param separator ','

4
--print @test -- result: I am 1 and you are 0

5
--select @test = [dbo].[FN_SPRINTF] ('I am %s and you are %s', '1#0', '#') --param separator ','

6
--print @test -- result: I am 1 and you are 0

7
8
SET ANSI_NULLS ON
9
GO
10
SET QUOTED_IDENTIFIER ON
11
GO
12
-- =============================================

13
-- AUTHOR: <SOVIT POUDEL>

14
-- =============================================

15
CREATE FUNCTION DBO.FN_SPRINTF
16
(
17
@STRING VARCHAR(MAX),
18
@PARAMS VARCHAR(MAX),
19
@PARAM_SEPARATOR CHAR(1) = ','
20
)
21
RETURNS VARCHAR(MAX)
22
AS
23
BEGIN
24
25
DECLARE @P VARCHAR(MAX)
26
DECLARE @PARAM_LEN INT
27
28
SET @PARAMS = @PARAMS + @PARAM_SEPARATOR   
29
SET @PARAM_LEN = LEN(@PARAMS)
30
WHILE NOT @PARAMS = ''
31
BEGIN
32
    SET @P = LEFT(@PARAMS+@PARAM_SEPARATOR, CHARINDEX(@PARAM_SEPARATOR, @PARAMS)-1)       
33
    SET @STRING = STUFF(@STRING, CHARINDEX('%S', @STRING), 2, @P)
34
    SET @PARAMS = SUBSTRING(@PARAMS, LEN(@P)+2, @PARAM_LEN)
35
END
36
RETURN @STRING
37
38
END

7. Printing Table Column Definitions

When comparing multiple databases that have similar schemas, one has to look at the details of table columns. The definitions of the columns (data types, nullables?) are as vital as the name of the columns themselves. 

Now for databases having many tables and tables having many columns, it can take quite a while to compare each column manually with a column from another table of another database. The next script can precisely be used to automate this very process as it prints the definitions of all tables for a given database.

1
SELECT
2
sh.name+'.'+o.name AS ObjectName,
3
s.name as ColumnName
4
,CASE
5
    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+')'
6
	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+')'
7
	WHEN t.name IN ('numeric') THEN t.name+'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')'
8
	ELSE t.name
9
END AS DataType
10
,CASE
11
	 WHEN s.is_nullable=1 THEN 'NULL'
12
	ELSE 'NOT NULL'
13
END AS Nullable       
14
	
15
FROM sys.columns s
16
INNER JOIN sys.types t ON s.system_type_id=t.user_type_id and t.is_user_defined=0
17
INNER JOIN sys.objects o ON s.object_id=o.object_id
18
INNER JOIN sys.schemas sh on o.schema_id=sh.schema_id
19
20
WHERE O.name IN
21
   (select table_name from information_schema.tables) 
22
23
ORDER BY sh.name+'.'+o.name,s.column_id

Conclusion

In this article, we looked at seven useful scripts that can cut down tons of manual, laborious work and increase overall efficiency for SQL developers. We also looked at different scenarios where these scripts can be implemented. 

If you're looking for even more SQL scripts to study (or to use), don't hesitate to see what we've got available on CodeCanyon.

Once you begin to get the hang of these scripts, certainly you will begin to identify many other scenarios where these scripts can be used effectively.

Good luck!

Advertisement
Did you find this post useful?
Want a weekly email summary?
Subscribe below and we’ll send you a weekly email summary of all new Code tutorials. Never miss out on learning about the next big thing.
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.