Batch scripting - SQL Server - Execute all sql files from a folder

Description:
In my project code base, I have a structure for all database objects (stored procedure, functions, trigger etc.) as well as some upgrade scripts. Need a nice way to deploy all the database scripts rather manually execute one by one.

Environment information:
1. Windows Batch script
2. SQL Server 2014

Script:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

@ECHO OFF REM Database connection info SET databaseName=db_name SET databaseServer=db_server SET databaseUserName=db_user SET databasePassword=db_password REM Location of scripts. Multiple folders can be specified by giving space in between and enclosed with quotation. REM This script will go through only one level of sub folders and execute all .sql files SET scriptLocation="location1" "location2" REM ----------------------------------------------------------- REM - Logic REM ----------------------------------------------------------- SET initialLocation=%cd% FOR %%e IN (%scriptLocation%) DO ( @ECHO ------------------------ @ECHO %%e @ECHO ------------------------ IF EXIST %%e ( CALL :executeScripts %%e FOR /D %%i IN (%%e\*) DO ( @ECHO ------------------------ @ECHO %%i @ECHO ------------------------ CALL :executeScripts "%%i" ) ) ) CD %initialLocation% PAUSE :: This function execute all the scripts from given path :executeScripts FOR %%G IN (%1\*.sql) DO ( @ECHO %%~nxG sqlcmd /S %databaseServer% /d %databaseName% -U %databaseUserName% -P %databasePassword% -i"%%G" ) EXIT /B 0

SQL Server - Setup - enable/disable SNAPSHOT isolation level

Environment:
1. SQL Server 2008

Background:
I need a script which would change isolation level to SNAPSHOT on current database. I tried to use CURRENT to make it work without much success. Instead used DB_NAME() built-in function to get the current database name and executed alter command as run time query.

Script snippet:

DECLARE @dbName VARCHAR(100) = '';
SELECT @dbName=DB_NAME();
IF ISNULL(@dbName, '') <> ''
BEGIN
DECLARE @cmd NVARCHAR(100) = 'ALTER DATABASE '+@dbName+'  SET ALLOW_SNAPSHOT_ISOLATION ON';
EXECUTE sp_executesql @cmd;
END;
GO


Helpful commands:

To enable:
ALTER DATABASE ABC SET ALLOW_SNAPSHOT_ISOLATION ON;

To disable:
ALTER DATABASE ABC SET ALLOW_SNAPSHOT_ISOLATION OFF;

How to check database isolation settings:
SELECT name
        , s.snapshot_isolation_state
        , snapshot_isolation_state_desc
        , is_read_committed_snapshot_on
        , recovery_model
        , recovery_model_desc
        , collation_name

FROM sys.databases s

SQL Server - Coding - Find out tables those are related

Environment: Tried on SQL Server 2008

First Query: Show all tables from the database where parent-child relationship is present 

SELECT
OBJECT_NAME(rkeyid) parent_table,
OBJECT_NAME(fkeyid) child_table,
OBJECT_NAME(constid) fkey_name,
c1.name fkey_col,
c2.name ref_keycol
FROM SYS.SYSFOREIGNKEYS s
INNER JOIN SYS.SYSCOLUMNS c1
ON ( s.fkeyid = c1.id and s.fkey = c1.colid )
INNER JOIN SYSCOLUMNS c2
ON ( s.rkeyid = c2.id and s.rkey = c2.colid )

ORDER BY parent_table,child_table
;


Second Query: Show all parent tables of a particular table

SELECT
OBJECT_NAME(rkeyid) parent_table,
OBJECT_NAME(fkeyid) child_table,
OBJECT_NAME(constid) fkey_name,
c1.name fkey_col,
c2.name ref_keycol
FROM SYS.SYSFOREIGNKEYS s
INNER JOIN SYS.SYSCOLUMNS c1
ON ( s.fkeyid = c1.id and s.fkey = c1.colid )
INNER JOIN SYSCOLUMNS c2
ON ( s.rkeyid = c2.id and s.rkey = c2.colid )
WHERE OBJECT_NAME(fkeyid) = ' '

ORDER BY parent_table,child_table


Third Query: Show all child tables of a particular table

SELECT
OBJECT_NAME(rkeyid) parent_table,
OBJECT_NAME(fkeyid) child_table,
OBJECT_NAME(constid) fkey_name,
c1.name fkey_col,
c2.name ref_keycol
FROM SYS.SYSFOREIGNKEYS s
INNER JOIN SYS.SYSCOLUMNS c1
ON ( s.fkeyid = c1.id and s.fkey = c1.colid )
INNER JOIN SYSCOLUMNS c2
ON ( s.rkeyid = c2.id and s.rkey = c2.colid )
WHERE OBJECT_NAME(rkeyid) = ' '

ORDER BY parent_table,child_table


Reference:
1. Concept is taken from an online article, unfortunately don't have link

TSQL - Coding - Dynamic query

Background: sp_executesql is used to execute dynamic query in SQL Server. Below few examples are given to demonstrate the use.

Table structure and data script:

DROP TABLE test;
GO


CREATE TABLE test (group_id INT, name VARCHAR(100));
GO
INSERT INTO test VALUES (1, 'First');
INSERT INTO test VALUES (1, 'Second');
INSERT INTO test VALUES (2, 'Third');
INSERT INTO test VALUES (2, 'Fourth');
INSERT INTO test VALUES (2, 'Fifth');
INSERT INTO test VALUES (3, 'Sixth');
GO

SELECT * FROM test;


Example-01: Use sp_executesql to execute a SELECT query

DECLARE @query NVARCHAR(200);
DECLARE @paramDifinition NVARCHAR(50);
DECLARE @value INT;

SET @query = N'SELECT * FROM wk_be_customer WHERE web_reference_id=@in';
SET @paramDifinition = N'@in INT';
SET @value = 233;

EXECUTE sp_executesql @query, @paramDifinition, @value;

Example-02: Use sp_executesql to execute a UPDATE query


-- Dynamic query - UPDATE
DECLARE @query NVARCHAR(200);
DECLARE @paramDifinition NVARCHAR(50);
DECLARE @value1 INT, @value2 INT;

SET @query = N'UPDATE test SET group_id=@in1 WHERE group_id=@in2';
SET @paramDifinition = N'@in1 INT, @in2 INT';
SET @value1 = 1;
SET @value2=2;

EXECUTE sp_executesql @query, @paramDifinition, @value1, @value2;


Example-03: Use sp_executesql to execute a UPDATE query with IN clause (string concatenation)


DECLARE @query NVARCHAR(200);
DECLARE @paramDifinition NVARCHAR(50);
DECLARE @value1 NVARCHAR(100), @value2 INT;

SET @value1 = '1,2';
SET @query = N'UPDATE test SET group_id=@in1 WHERE group_id IN ('+@value1+')';
SET @paramDifinition = N'@in1 INT';
SET @value2=3;

EXECUTE sp_executesql @query, @paramDifinition, @value2;


Notes:
1. sp_executesql - input parameters have to be NVARCHAR type

Resources:
1. http://msdn.microsoft.com/en-us/library/ms188001.aspx

T-SQL Programming - Useful Tips - Dynamic Query

Code-1: Dynamic query checks column name exists in the table


DECLARE 
@tableName VARCHAR(100) = 'table_name',
@targetColumnName VARCHAR(100) = 'column_name',
@columnExists SMALLINT = 0,
@sql NVARCHAR(MAX)
;

SET @sql = 'SELECT @cnt=COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '''+@tableName+''' AND COLUMN_NAME = '''+@targetColumnName+'''';
PRINT @sql;
EXECUTE sp_executesql @sql, N'@cnt SMALLINT OUTPUT',@columnExists OUTPUT;

Points to consider:

1. All text parameters for sp_executesql have to be 'N' type (Note: string to the code page that corresponds to the default collation of the database or column) (See Code-1, Code-2)
2. Parameter (in/out) definition count has to match with parameters provided (see Code-2, four parameters declared for dynamic query, two are in and two are out parameters,)
3. If there are any output type parameters defined, than corresponding parameters have to mark as OUTPUT also (See Code-2)
4. Input or output parameters assignment can have same or different name (see Code-2, all variables used inside dynamic query and declared outside match with corresponding one)

Code-2: Dynamic query takes two input parameters, does two mathematics operations and returns results in two output parameters

DECLARE 

@inParam1 INT = '2',

@inParam1 INT = '4',

@outParam1 INT,

@outParam2 INT,

;

SET @sql = 'SELECT @inParam1, @inParam2';
SET @sql = @sql + 'SET @outParam1=@inParam1 + @inParam2';
SET @sql = @sql + 'SET @outParam1=@inParam1 * @inParam2';
PRINT @sql;

EXECUTE sp_executesql @sql, N'@inParam1 INT, @inParam2 INT,@outParam1 INT OUTPUT, @outParam2 INT OUTPUT', @inParam1, @inParam2, @outParam1 OUTPUT, @outParam2 OUTPUT;

T-SQL Programming - Useful Tips - Text Search

SQL Server 2008 R2

TEXT SEARCH

Case Sensitive Search
1. CHARINDEX('ExpressionToFind', 'ExpressionToSearch' COLLATE Latin1_General_CI_AS)

    Here CHARINDEX function returns the position of the string found in  the search. Returns 0 if not found.

Case Insensitive Search
1. Using CHARINDEX

CHARINDEX('ExpressionToFind', 'ExpressionToSearch' COLLATE Latin1_General_CS_AS)

 - Here CHARINDEX function returns the position of the string found in  the search. Returns 0 if not found.

2. Using Like 
IF EXISTS(SELECT * FROM (SELECT 'This is a test' a) tab WHERE a like '%test')

- 'Like' does case insensitive search in T-SQL