:::: MENU ::::

Posts Categorized / T-SQL Scripts

  • Oct 02 / 2017
  • 0
T-SQL Enhancements, T-SQL Interview Questions, T-SQL Scripts, T-SQL Tips and Tricks

Exec vs Sp_ExecuteSQL for dynamic SQL execution

Exec and sp_executeSQL both are used for executing the dynamic sql statements. There are some differences between the both

EXEC

Exec is available since early versions before the sp_executesql. It has no direct support of using or storing the local variables. Also using Exec makes the setup vulnerable to sql injection attacks as parameters appear as part of code.

SP_ExecuteSQL

sp_executesql is improved way to execute dynamic sql statements. It provides mechanism to use and store values in local variables. This mechanism is not vulnerable to sql injection as variables do not appear as part of code but as an operand. Also it enables the sql engine to use the cached execution plan of the statement like a stored procedure.

Conclusion

sp_executesql has upper hand as compared to exec method of executing dynamic sql. Keeping in view the flexibility, efficiency and security of sp_executesql we should opt it for executing dynamic SQL.

  • Aug 31 / 2017
  • 0
T-SQL Scripts, Table Partitioning

Verify next month partitions in all databases

I have databases with partitioned tables in some databases. I occasionally had to verify that next month partition is there in each database. It required to check in each database one by one. To improve the process i created a script that parses the databases with partitioned tables one by one and checks either partition for next month data is there or not.

Specifications

My created script is specific to scenario and following are the specifications

  1. All partitioned tables in a DB are created on a single partition scheme. Hence there is only one partition scheme and partition function in a DB.
  2. Partitions are created with one month span. i.e. Each partition has one month data.
  3. Partitions are created with right boundary (less than)
  4.  Script checks either partition for next months data is added or not

Script



SET NOCOUNT ON
GO

Print 'Report for partitioning status on '+@@servername
-- Create tempDB to hold DB names with partitioned tables
IF OBJECT_ID(N'tempdb.dbo.#DBs') IS NOT NULL
	DROP TABLE #DBs

CREATE TABLE #DBs (DBName VARCHAR(50))

-- Insert DB names with partitioned tables
INSERT INTO #DBs (DBName)
EXEC sp_msforeachdb 
'use ?; if exists (select top 1 * from ?.[sys].[partitions] where partition_number >1) select db_name()'
GO

-- Declare variables
DECLARE @DB_Name VARCHAR(50)
DECLARE @Command NVARCHAR(1000)
-- Declare cursor for checking DBs one by one
DECLARE database_cursor CURSOR
FOR

SELECT DBname
FROM #DBs

OPEN database_cursor

FETCH NEXT
FROM database_cursor
INTO @DB_Name

WHILE @@FETCH_STATUS = 0
BEGIN
	-- Prepare sql for last partition check
	SELECT @Command = 'use ' + @DB_Name + ';
	 if (SELECT convert(bigint, max(r.value) ) 
	 FROM [sys].[partition_range_values] r inner join [sys].[partition_functions] f
  on r.function_id = f.function_id) 
  < (select CONVERT([bigint],replace
  (replace(CONVERT([varchar](13),DATEADD(DAY, 1,(DATEADD(MONTH,1 , EOMONTH(getdate())))),(121)),''-'',''''),'' '','''')+''00''))
  Print ''Partitioning issue in DB ' + @DB_Name + ''';' + 'else print ''Partitioning Ok in DB ' + @DB_Name + ''' ;'

	--print @Command;
	EXEC sp_executesql @Command;

	FETCH NEXT
	FROM database_cursor
	INTO @DB_Name
END

CLOSE database_cursor

DEALLOCATE database_cursor

GO

Output

Output will be in following format.

Verify next month partitions in all databases

 

  • Aug 25 / 2017
  • 0
Columnstore Indexes, T-SQL Scripts, T-SQL Tips and Tricks

Row count for all tables with clustered columnstore index in a database

Consider a scenario that you require to get number of rows for tables in a database with clustered column store index (CCI). It would require to access three tables to get the required info. Following script will get the row count for all tables with CCI

-- Get row count for all tables with CCI in a DB
SELECT schema_name(o.schema_id)+'.'+o.name AS CCITables,
sum(si.rowcnt) AS NoOfRows
FROM sys.objects o
INNER JOIN sysindexes si ON o.object_id = si.id
INNER JOIN sys.indexes i ON o.object_id = i.object_id
AND i.type = 5
GROUP BY schema_name(o.schema_id)+'.'+o.name
ORDER BY NoOfRows DESC
GO

  • Mar 09 / 2017
  • 0
Database Migrations, dbDigger, T-SQL Scripts

Code snippets for database migration task

Following are some code snippets that i used during a database migration task from one server to another. Backup restore method is used in this migration task

DB list on source server

Get the database list on source server that are required to migrate

select name, user_access, state_desc, create_date
from sys.databases
where name in
( 'comma separated list of specific databases to be migrated')
order by name

Note DB properties

Note the important database properties on source server so that we may verify these on destination server after migration is complete.

select name, is_published, is_subscribed, is_cdc_enabled, is_trustworthy_on, is_read_only

from sys.databases where name in
( 'comma separated list of specific databases to be migrated')
order by name

Size of DBs

Get size of all databases that are to be migrated. It would help to estimate the disk capacity on destination and also the time estimate for backup and restore process.

SELECT DB_NAME(database_id) AS DatabaseName, type_desc,
cast(sum( size* 8.0 / 1024)/1024 as DECIMAL(18,3)) [Size(GB)]
FROM sys.master_files
where DB_NAME(database_id) in
( 'comma separated list of specific databases to be migrated')
GROUP BY DB_NAME(database_id), type_desc
ORDER BY DB_NAME(database_id), type_desc DESC

Get which DBs are in Avaiability Group

SELECT
AG.name AS [AvailabilityGroupName],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
dbcs.database_name AS [DatabaseName]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
ON arstates.replica_id = dbcs.replica_id
where dbcs.database_name in ('Comma seperated list of DBs to be migrated')

Set DBs to READ ONLY and Restricted mode on source server

Before backing up the databases we will do following operations for each DB. As here i have databases in AG so i also included command to set DB out of AG so that i may put it to READ ONLY mode next.

ALTER AVAILABILITY GROUP [AvailabilityNodeName] REMOVE DATABASE [DBName]
GO
ALTER DATABASE [DBName] SET  READ_ONLY WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [DBName] SET  RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO

Backup Databases

We will take backup of all databases through following script. Go through the option used for backup and also put a valid path. Backup file name will be generated automatically.

-- Declare cursor for database names
DECLARE BackupCursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name in
( 'Comma seperated list of databases')

-- Declare variable to hold DBName
declare @Db_name varchar(100)

OPEN BackupCursor
FETCH NEXT FROM BackupCursor INTO @Db_name

WHILE (@@FETCH_STATUS &lt;&gt; -1)
BEGIN

-- Backup code
declare @Backup_Path Varchar(500) = 'Full Backup Path here'

DECLARE @fileName VARCHAR(400)  = @Backup_Path +'\'+ replace(@@servername, '\','_' ) + '_' + @Db_name+ '_' + 'FULL'+ '_' + LEFT(CONVERT(VARCHAR,getdate(), 120), 10)
+'.BAK'   -- filename for backup

DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'BACKUP DATABASE ['+ @Db_name+'] TO DISK ='+   ''''+@fileName+ ''' WITH COPY_ONLY, CHECKSUM, INIT,COMPRESSION, STATS = 10;'

EXEC (@sqlCommand)

RESTORE  verifyonly from disk = @fileName
print 'Backup file path is '+@fileName

FETCH NEXT FROM BackupCursor INTO @Db_name
END
-- Close and deallocate the cursor
CLOSE BackupCursor
DEALLOCATE BackupCursor

 

Get restore command for each DB one by one

Put DB name in first line of script one by one and get the restore commands for last full backup of that specific database

     declare @db_name varchar(100) = 'DBName'

Declare @tmp table
(StartDT datetime, BKP_Type char(1), RestorePart Varchar(2000), FilePart varchar(max))

If exists (select name from sys.databases where name = @Db_name)

begin
Insert @tmp
SELECT s.[backup_start_date] ,s.[type]
,'Restore database ' + s.[database_name] +'
From Disk = ''' + Replace (replace (B.[physical_device_name], 'SQL Backups 2', 'SQL Backups 2'), 'DROPOFF', 'RECOVERY') + '''
with replace, keep_cdc, recovery, stats =1 ' as RestorePart    ,
'      , Move ''' + f.[logical_name] + ''''
+ ' TO ''' + f.[physical_name] + '''' as FilePart
FROM [msdb].[dbo].[backupset] S
inner join [msdb].[dbo].[backupfile] F
on S.backup_set_id = f.backup_set_id
inner join [msdb].[dbo].[backupmediafamily] B
on s.media_set_id = b.media_set_id
where s.type ='d'
and s.database_name = @Db_name
and s.backup_start_date =
(select max(s.backup_start_date)
from [msdb].[dbo].[backupset] s
where s.type ='d' and s.database_name = @Db_name )
order by s.backup_set_id desc

--select * from @tmp

select distinct '-- Latest Full backup for database ' + @Db_name + ' is from ' + cast(StartDT as varchar(20))
from @tmp

Select '--Run this on DR replica for initial restore:' as [--RestoreStatement]
union all
Select Distinct RestorePart
from @tmp
union all
select FilePart from @tmp

End
ELSE
Select 'Database ' + @Db_name + ' does not exist on ' + @@Servername
Set nocount off

Set databases to READ WRITE mode on destination server

ALTER DATABASE [AD_MgmtMaster] SET  MULTI_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [AD_MgmtMaster] SET  READ_WRITE WITH ROLLBACK IMMEDIATE
GO

 

Fix and verify the orphan users

</pre>
-- Fix orphaned users in DB
<pre>EXEC     [dbo].[spFixOrphanUsers]    @DatabaseNM = 'DBNameHere'
GO

-- Verify the orphaned users in DB
USE DBNameHere
GO
sp_change_users_login @Action='Report'
GO

 

Update the DB properties

Following scripts will generate commands to fix the specific database property.

-- Set DB owner to sa
select suser_sname(owner_sid) , 'ALTER AUTHORIZATION ON DATABASE::'+name+'  TO sa;'
from sys.databases
where suser_sname(owner_sid) &lt;&gt; 'sa'

-- Set compatibility level
select 'ALTER DATABASE ['+name+'] SET COMPATIBILITY_LEVEL = 120;'
from sys.databases
where COMPATIBILITY_LEVEL &lt;&gt; 120

-- Set AutoCreateStats
select 'ALTER DATABASE ['+name+'] SET AUTO_CREATE_STATISTICS ON;'
from sys.databases
where is_auto_create_stats_on = 0

-- Set AutoUpdateStats
select 'ALTER DATABASE ['+name+'] SET AUTO_UPDATE_STATISTICS ON;'
from sys.databases
where is_auto_update_stats_on = 0

 

 

  • Oct 01 / 2014
  • 0
dbDigger, Dynamic Management Views DMV, Monitoring and Analysis, SQL Server IO, T-SQL Scripts

Get number of reads and writes for each database on SQL Server

We can get the number of read and write operations for each database on our server. This information id being fetched by a DMV so analysis data depends upon up time of server.

 -- total I/O for each database  
 SELECT name AS 'Database Name'  
 ,SUM(num_of_reads) AS 'Number of Read'  
 ,SUM(num_of_writes) AS 'Number of Writes'   
 FROM sys.dm_io_virtual_file_stats(NULL, NULL) I  
 INNER JOIN sys.databases D   
 ON I.database_id = d.database_id  
 GROUP BY name ORDER BY 'Number of Read' DESC;  
  • Oct 01 / 2014
  • 0
dbDigger, T-SQL Scripts, T-SQL Tips and Tricks

Increase or shrink file size for SQL Server database files

Following handy scripts help to manage the SQL Server database files. You may increase the max size and current size as well.

-- Increase file max size  
 USE [master];  
 ALTER DATABASE DatabaseNameHere MODIFY FILE ( NAME = N'FileNameHere', maxsIZE = 1500MB)  
 GO  
 -- Increase file size  
 USE [master];  
 ALTER DATABASE DatabaseNameHere MODIFY FILE ( NAME = N'FileNameHere', sIZE = 1500MB)  
 GO  

Shrink operation may be up to a specific extent or just shrink by removing the space available ay the end of file without manipulating data in file.If file shrink operation is cancelled during execution the current status is not rolled back. TRUNCATEONLY operation is fast.

-- Shrink to specific limit  
 USE DatabaseNameHere;  
 DBCC SHRINKFILE (N'FileNameHere' , 200)  
 GO  
 -- Shrink to space available at the end of file  
 USE DatabaseNameHere;  
 DBCC SHRINKFILE (N'FileNameHere' , 0, TRUNCATEONLY)  
 GO  
Consult us to explore the Databases. Contact us