SQL Scripts

Miscelleneous Scripts for SQL

Count Number of Rows for Tables of a Database

Easiest way to get an exact value of Number of Rows for all the tables in a SQL Server database

1. Use DBCC UPDATEUSAGE - this updates the values of rows for each partition in a table.
2. Use undocumented stored procedure sp_msForEachTable and store the result set in a table.

Below is the query to get required output:

USE [DatabaseName]
GO

DECLARE @DynSQL NVARCHAR(255)
SET @DynSQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'
EXEC(@DynSQL)

IF OBJECT_ID('tempdb..#T','U') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T (TableName nvarchar(500),NumberOfRows int)
GO

INSERT INTO #T
EXEC sp_msForEachTable 'SELECT PARSENAME(''?'', 1) as TableName,COUNT(*) as NumberOfRows FROM ?'
GO
SELECT * FROM #T ORDER BY NumberOfRows DESC

T-SQL script for BULK INSERT

Here is the T-SQL code to BULK insert data from text file to SQL Table:

BULK INSERT [Database]..[TableName]

FROM 'D:\test.txt' -- Path of text file
WITH
(
   FIRSTROW = 1
   ,BATCHSIZE = 10000
   ,FIELDTERMINATOR = ','
   ,ROWTERMINATOR = '\n'
   ,LASTROW = 20
)
GO

Here is the description of Keywords used:

FIRSTROW
Specifies the number of the first row to load. The default is the first row in the specified data file.

BATCHSIZE
Specifies the number of rows in a batch. Each batch is copied to the server as one transaction. If this fails, SQL Server commits or rolls back the transaction for every batch. By default, all data in the specified data file is one batch.

FIELDTERMINATOR
Specifies the field terminator to be used for char and widechar data files. The default field terminator is \t.

ROWTERMINATOR
Specifies the row terminator to be used for char and widechar data files. The default row terminator is \r\n (newline character).

LASTROW
Specifies the number of the last row to load. The default is 0, which indicates the last row in the specified data file.

Time Dimension

After posting script for Date Dimension, I thought of sharing script for Time Dimension as well. Some times we need to drill down the Cube data by time hierarchy e.g. AM/PM-->Hrs-->Min. Below script will create Time dimension and solve the purpose.

USE [DatabaseName]
GO

IF OBJECT_ID('DimTime') IS NOT NULL
DROP TABLE DimTime
GO


CREATE TABLE [dbo].[DimTime]
(
   [DimTimeSK] [int] NOT NULL,
   [Time] [varchar](11) NOT NULL,
   [Time24] [varchar](8) NOT NULL,
   [HourName] [varchar](5),
   [MinuteName] [varchar](8),
   [Hour] [tinyint],
   [Hour24] [tinyint],
   [Minute] [tinyint],
   [Second] [int],
   [AM] [char](2)
) ON [PRIMARY]

GO


DECLARE
   @DimTimeSK int,@Date datetime, @AM char(2),
   @hour24 tinyint, @hour tinyint,
   @minute tinyint, @second int

SET @DimTimeSK = 0

WHILE @DimTimeSK < (60*60*24)
BEGIN
   SET @DimTimeSK = @DimTimeSK + 1
   SET @Date = DATEADD(second,@DimTimeSK,convert(datetime, '1/1/2007'))
   SET @AM = right(convert(varchar,@Date,109),2)
   SET @hour24 = DATEPART(hour, @Date)
   SET @hour = CASE WHEN @AM = 'PM' THEN @hour24 - 12 ELSE @hour24 END
   SET @minute = DATEPART(minute, @Date)
   SET @second = DATEPART(second, @Date)

   INSERT INTO dbo.DimTime
   (
      [DimTimeSK]
      ,[Time]
      ,[Time24]
      ,[HourName]
      ,[MinuteName]
      ,[Hour]
      ,[Hour24]
      ,[Minute]
      ,[Second]
      ,[AM]
   )

   SELECT
   @DimTimeSK AS [DimTimeSK]
   ,right('0'+ convert(varchar,@hour),2) + ':' +
   right('0'+ convert(varchar,@minute),2) + ':' +
   right('0'+ convert(varchar,@second),2) + ' ' + @AM AS [Time]
   ,convert(varchar,@Date,108) [Time24]
   ,right('0' + convert(varchar,@hour),2) + ' ' + @AM AS [HourName]
   ,right('0' + convert(varchar,@hour),2) + ':' +
   right('0' + convert(varchar,@minute),2)+ ' ' + @AM AS [MinuteName]
   ,@hour AS [Hour]
   ,@hour24 AS [Hour24]
   ,@minute AS [Minute]
   ,@second AS [Second]
   ,@AM AS [AM]
END
GO

Date Dimension

Below is the script to create Date Dimension.

USE [DatabaseName]
GO

IF OBJECT_ID('Date','U') IS NOT NULL
DROP TABLE Date
GO


CREATE TABLE [dbo].[Date](
   [DateSK] [int] NOT NULL,
   [FullDate] [datetime] NOT NULL,
   [DateName] [char](11) NOT NULL,
   [DayOfWeek] [tinyint] NOT NULL,
   [DayNameOfWeek] [char](10) NOT NULL,
   [DayOfMonth] [tinyint] NOT NULL,
   [DayOfYear] [smallint] NOT NULL,
   [WeekdayWeekend] [char](7) NOT NULL,
   [WeekOfYear] [tinyint] NOT NULL,
   [MonthName] [char](10) NOT NULL,
   [MonthOfYear] [tinyint] NOT NULL,
   [CalendarQuarter] [tinyint] NOT NULL,
   [CalendarYear] [smallint] NOT NULL,
   [CalendarYearMonth] [char](7) NOT NULL,
   [CalendarYearQtr] [char](15) NOT NULL,
CONSTRAINT PK_Date_DateID PRIMARY KEY (DateSK)
) ON [PRIMARY]
GO
RAISERROR('Table Date created successfully!',0,1)

DECLARE @StartDate datetime, @EndDate datetime
-- Set StartDate and EndDate as per your requirement
SELECT @StartDate = '2009-01-01', @EndDate = '2010-12-31'

WHILE (@StartDate <= @EndDate )
BEGIN
INSERT INTO Date
SELECT
   CAST(CONVERT(varchar(8),@StartDate,112) AS int) DateSK
   ,@StartDate AS [Date]
   ,CONVERT(varchar(20),@StartDate,106) AS DateName
   ,DATEPART(DW,@StartDate)   [DayOfWeek]
   ,DATENAME(DW,@StartDate) [DayNameOfWeek]
   ,DATENAME(DD,@StartDate)  [DayOfMonth]
   ,DATENAME(DY,@StartDate)  [DayOfYear]
   ,CASE WHEN DATEPART(DW,@StartDate) IN (1,7) THEN 'WeekEnd'
             ELSE 'WeekDay' END     [WeekdayWeekend]
   ,DATEPART(WW,@StartDate) [WeekOfYear]
   ,DATENAME(MM ,@StartDate) [MonthName]
   ,DATEPART(MM ,@StartDate)   [MonthOfYear]
   ,DATEPART(QQ,@StartDate)     [CalendarQuarter]
   ,DATEPART(YY ,@StartDate)    [CalendarYear]
   ,DATENAME(YY,@StartDate)+'-'+RIGHT('0'+CAST(Month(@StartDate) as varchar),2)  [CalendarYearMonth]
   ,DATENAME(YY,@StartDate)+'-Q'+DATENAME(QQ,@StartDate) [CalendarYearQtr]

   SET @StartDate = @StartDate +1
END
GO


Date Dimension is ready to use as soon as you execute this script in required database.
You can create following Calendar Hierarchy in your dimension to provide drill down featrues for Growth, Trends, and ToDate calculations (e.g. YTD, QTD, MTD).
Year-->Quarter-->Month-->Week-->Date

Find Largest Size Tables in a Database

Below is the stored procedure to return largest tables of a database.

IF OBJECT_ID('sp_LargestTables' ,'P') IS NOT NULL
DROP PROC sp_LargestTables
GO
/***************************************************************
CREATE BY : Hari Sharma
PURPOSE : To get a list of tables according to their size.
***************************************************************/
CREATE PROC sp_LargestTables
(
   @n int = NULL,
   @IsSystemAllowed bit = 0
)
AS
BEGIN
   SET NOCOUNT ON
   DECLARE @LOW int
   SELECT @LOW = LOW
   FROM [master].[dbo].[spt_values] (NOLOCK)
   WHERE [number] = 1 AND [type] = 'E'

   IF @n > 0 SET ROWCOUNT @n
   SELECT TableName,[Row Count],[Size (KB)] FROM
   (
      SELECT QUOTENAME(USER_NAME(o.uid)) + '.' +
                     QUOTENAME(OBJECT_NAME(i.id))
         AS TableName
         ,SUM(i.rowcnt) [Row Count]
         ,CONVERT(numeric(15,2),
            (((CONVERT(numeric(15,2),SUM(i.reserved)) * @LOW) / 1024.0))) AS [Size (KB)]
      FROM sysindexes i (NOLOCK)
      INNER JOIN sysobjects o (NOLOCK)
         ON i.id = o.id AND
         ((@IsSystemAllowed = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
      WHERE indid IN (0, 1, 255)
      GROUP BY
         QUOTENAME(USER_NAME(o.uid)) + '.' +
         QUOTENAME(OBJECT_NAME(i.id))
   ) AS Z
   ORDER BY [Size (KB)] DESC

   SET ROWCOUNT 0
END
GO


How to use:

1. If you want all the user tables in the database with largest db size then:
EXEC sp_LargestTables [No Need to pass parameters]

2. If you want only 3 tables in the database with largest db size then:
EXEC sp_LargestTables 3

3. If you want only 20 tables in the database with largest db size including system tables then:
EXEC sp_LargestTables 20,1

Track SQL Database Growth

In this article I am sharing a simple T-SQL code to track database growth for specific database. This could be a very simple query for SMEs but it can really help newbies:

/*************************************************
Purpose : Track Database Growth for a specific DB
Create By : Hari Sharma
**************************************************/
SELECT
   BackupDate =
   CONVERT(VARCHAR(10),backup_start_date, 111)
   ,SizeInMBs=FLOOR(backup_size/1024000)
FROM msdb..backupset
WHERE
   database_name = DB_NAME() --Specify DB Name
   AND type = 'd'
ORDER BY
   backup_start_date desc

Moving Database Files From One Drive to Another

In practice, database files grows everyday. Sometimes it occupy the complete disk and we may end up in unsufficeint mamroy - leading to unexpected results.

In this blog, I'll explain how to transfer database files from one drive to another. To explain, I'll create a test database TestDB. Here is the command:

USE [master]
GO

IF DB_ID(N'TestDB') IS NULL
BEGIN
CREATE DATABASE [TestDB] ON PRIMARY
(
   NAME = N'TestDB'
   ,FILENAME = N'C:\MSSQL\Data\TestDB.mdf'
   ,SIZE = 3MB
   ,MAXSIZE = 2048GB
   ,FILEGROWTH = 1024KB
)
LOG ON
(
   NAME = N'TestDB_log'
   ,FILENAME = N'C:\MSSQL\Data\TestDB.ldf'
   ,SIZE = 3MB
   ,MAXSIZE = 2048GB
   ,FILEGROWTH = 10%
)
END
GO

Now check existing files location:

USE TestDB
GO
SELECT name, physical_name, state_desc
FROM TestDB.sys.master_files
WHERE database_id = DB_ID(N'TestDB')
 

 
 
 
 
   
 
Now the database is created, you can create some tables and enter some data into the database, if you wish, Otherwise proceed like this:
 
Step1: Make the database OFFLINE.

USE [master]
GO
ALTER DATABASE TestDB SET OFFLINE

Step2: Move file Physically
Now you need to move that file physically from existing folder to the new the location. Open the parent folder (Here 'C:\MSSQL\DATA') , You can see both mdf and ldf files', make sure that you cut the appropriate file, in this example it is the Log file. Cut that "TestDB_log.LDF" file and paste it on "D:\MSSQL\Data"
 
Step3: Update the system reference
Once you move the file physically , you need to update the system reference using the ALTER DATABASE .. MODIFY FILE Command as shown below:
 
ALTER DATABASE TestDB
MODIFY FILE (
   NAME ='TestDB_log'
   ,FILENAME = 'D:\MSSQL\Data\TestDB.LDF'
)

Step5 : Make database ONLINE
Last step is to make database online as shown below:

ALTER DATABASE TestDB SET ONLINE
GO

CONCAT() IN SQL SERVER 2012

SQL Server 2012 introduces a new string function CONCAT() which allows you to concatenate up to 255 string or variable values in to one single string. It requires a minimum of two input values. Good thing about the function is that it takes care of implicitly converting the data values to a string by following the data type conversion rules of SQL Server 2012. This function would help eliminate the need of explicit data conversions when concatenating two values.

 Note: NULL values are implicitly converted to an empty string. If all the variables passed as parameters in CONCAT function are NULL, an empty string of type VARCHAR(1) is returned.

SYNTAX:

SELECT CONCAT (String_Value1, String_Value2, String_Value3 [, String_ValueN])
-- Between 2 and 254 values can be passed.

EXAMPLE:

DECLARE @FirstName NVARCHAR(10) = N'हरि'
       ,@LastNameVARCHAR(10) = 'Sharma'
       ,@Cell VARCHAR(20) = '+1 (425) XXX-YYYY'
       ,@Age INT = 30
       ,@Delimiter VARCHAR(2) = SPACE(2)
SELECT CONCAT(@FirstName, @Delimiter, @LastName, @Delimiter, @Cell, @Delimiter, @Age)

OUTPUT:

　

You might have observed that there’s no data conversion being performed in the above example.

The data returned by CONCAT function depends on the type of valued passed. Below are some of the mapping input and output types:

 

Input Value / Type	Output Value / Type
SQL-CLR (TYPES & UDT)	NVARCHAR(MAX)
NVARCHAR(MAX)	NVARCHAR(MAX)
NVARCHAR(<=4000)	NVARCHAR(<=4000)
VARBINARY(MAX)	NVARCHAR(MAX)
ALL OTHER INPUT TYPES	VARCHAR(<=8000) *Unless one of the parameters is an NVARCHAR of any length and in that case, the output value would be in NVARCHAR(MAX)

NOTE: CONCAT function only works with SQL Server 2012 and later versions.

How to find missing Identity/Sequence numbers using T-SQL?

SQL Developers, did you ever come across a situation where you need to find missing identity or sequence number for a given table? For instance, someone deleted few records from a table which has an IDENTITY column. Have you wondered how to find those missing rows? In this blogpost, I am going to explain different ways to find missing identity or sequence numbers.

First I will create some sample data for demonstration purpose.

-- Prepare Test data --
SET NOCOUNT ON
IF OBJECT_ID('dbo.TestData') IS NOT NULL    DROP TABLE dbo.TestData

CREATE TABLE dbo.TestData (

    ID INT IDENTITY(1, 1) NOT NULL
)

DECLARE @Counter INT = 1
WHILE @Counter <= 15
BEGIN
    INSERT dbo.TestData DEFAULT VALUES
    SET @Counter += 1
END
 

SELECT ID FROM dbo.TestData
 

  

Now lets delete some rows manualy:
-- Now delete some records (IDs)
DELETE dbo.TestData WHERE ID IN (3,7,8,10,12,13)
 
--Verifiy the data
SELECT ID FROM dbo.TestData

Below are three different ways to identity missing values.

Find missing sequence numbers using Ranking Function:

-------------------------------------------

-- Option 1: Using Ranking function

-------------------------------------------
DECLARE @MaxID INT = (SELECT MAX(ID) FROM dbo.TestData)

SELECT SeqID AS MissingSeqID

FROM (SELECT ROW_NUMBER() OVER (ORDER BY column_id) SeqID from sys.columns) LkUp
LEFT JOIN dbo.TestData t ON t.ID = LkUp.SeqID

WHERE t.ID is null and SeqID < @MaxID

Here is the output:

 -- If there are less records in sys.columns and

-- you need need larger result then use CROSS JOIN

SELECT SeqID AS MissingSeqID

FROM (SELECT ROW_NUMBER() OVER (ORDER BY c1.column_id) SeqID

FROM sys.columns c1

CROSS JOIN sys.columns c2) LkUp

LEFT JOIN dbo.TestData t ON t.ID = LkUp.SeqID

WHERE t.ID is null and SeqID < @MaxID

Find missing sequence numbers using CTE:

-------------------------------------------

-- Option 2: Using Common Table Expression

-------------------------------------------

DECLARE @MaxID INT = (SELECT MAX(ID) FROM dbo.TestData)

;WITH CTE (MissingSeqID, MaxID)

AS (

    SELECT 1 AS MissingSeqID, @MaxID

    UNION ALL

    SELECT MissingSeqID + 1, MaxID FROM CTE WHERE MissingSeqID < MaxID

    )

SELECT MissingSeqID FROM CTE

LEFT JOIN dbo.TestData t on t.ID = CTE.MissingSeqID

WHERE t.ID is NULL

GO

Find missing sequence numbers using Tally Table:
This is the most prefered way out of all the above options.

-------------------------------------------

-- Option 3: Using Tally Table

-------------------------------------------

DECLARE @MaxID INT = (SELECT MAX(ID) FROM dbo.TestData)

SELECT t.ID  MissingSeqID FROM dbo.Tally t

LEFT JOIN dbo.TestData td

ON td.ID = t.ID

WHERE td.ID IS NULL

AND t.ID < @MaxID

Populate sequence numbers in a table:

DROP TABLE NumbersTestDECLARE @RunDate datetimeSET @RunDate=GETDATE()CREATE TABLE NumbersTest(Number INT IDENTITY(1,1)) SET NOCOUNT ONWHILE COALESCE(SCOPE_IDENTITY(), 0) < 100000BEGIN     INSERT dbo.NumbersTest DEFAULT VALUES END