SQL: Count number of records in each table within a specific list of databases

The Transact-SQL script below, returns a list of tables for a specific list of database, and a count of the number of records within each table.

Please ensure you test any script taken from my website on a test/development machine, before running on a production server

USE Master
GO
--Table to hold the list of databases:
CREATE TABLE #DatabasesToCount
( DBName VARCHAR(100) NOT NULL PRIMARY KEY)
CREATE TABLE #TableRecordCount
( DBName VARCHAR(100), TableName VARCHAR(100), RecordCount INT)
INSERT INTO #DatabasesToCount (DBName) VALUES ('Database1') -- Change to specific database 1
INSERT INTO #DatabasesToCount (DBName) VALUES ('Database2') -- Change to specific database 2
INSERT INTO #DatabasesToCount (DBName) VALUES ('DatabaseX') -- Change to specific database 3
DECLARE @Database VARCHAR(100)
DECLARE @TableName VARCHAR(500)
DECLARE @SQL NVARCHAR(1000)
DECLARE db CURSOR LOCAL FAST_FORWARD
FOR SELECT QUOTENAME(DBName) FROM #DatabasesToCount
OPEN db
FETCH NEXT FROM db INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @SQL = 'DECLARE tbl CURSOR GLOBAL FAST_FORWARD FOR '
  SET @SQL = @SQL + 'SELECT QUOTENAME(S.name) + ' + CHAR(39) + '.' + CHAR(39) + ' + QUOTENAME(T.name) '
  SET @SQL = @SQL + 'FROM ' + @Database + '.sys.tables T '
  SET @SQL = @SQL + 'JOIN ' + @Database + '.sys.schemas S ON T.schema_id = S.schema_id '
  SET @SQL = @SQL + 'ORDER BY 1'
  PRINT @SQL
  EXECUTE sp_executeSQL @SQL
  OPEN tbl
  FETCH NEXT FROM tbl INTO @TableName
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @sql = 'INSERT INTO #TableRecordCount (DBName, TableName, RecordCount) '
    SET @sql = @sql + 'SELECT ''' + @Database + ''' AS DBName, ''' + @TableName + ''' AS TableName, COUNT(*) AS CNT FROM ' + @Database + '.' + @TableName
    EXEC (@sql)
    FETCH NEXT FROM tbl INTO @TableName
  END
  CLOSE tbl
  DEALLOCATE tbl
  FETCH NEXT FROM db INTO @Database
END
CLOSE db
DEALLOCATE db
SELECT * FROM #TableRecordCount
DROP TABLE #TableRecordCount
DROP TABLE #DatabasesToCount
GO

MERGE Transact-SQL statement example

I have only just found out about the “MERGE” transact-SQL statement that was introduced in SQL Server 2008.

From https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15

Runs insert, update, or delete operations on a target table from the results of a join with a source table. For example, synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

To investigate this further, I have created a “MergeExample” database on my local instance, and created a Beverages table, and an Updates table to hold the new version of Beverages:

Please ensure you test any script taken from my website on a test/development machine, before running on a production server

USE MergeExample
GO
IF OBJECT_ID('dbo.Beverages', 'U') IS NOT NULL DROP TABLE dbo.Beverages
GO
CREATE TABLE dbo.Beverages (
	BeverageID INT NOT NULL PRIMARY KEY, BeverageName VARCHAR(100) NOT NULL,
	DateAdded DATE NULL, DateUpdated DATE NULL)
GO
INSERT INTO dbo.Beverages (BeverageID, BeverageName, DateAdded)
VALUES	(1, 'Coffee', '2018-01-01'),
		(2, 'Tea', '2018-01-01'),
		(3, 'Water', '2018-01-01'),
		(4, 'Biscuits', '2018-01-01')
GO
IF OBJECT_ID('dbo.Updates', 'U') IS NOT NULL DROP TABLE dbo.Updates
GO
CREATE TABLE dbo.Updates (
	BeverageID INT NOT NULL PRIMARY KEY, BeverageName VARCHAR(100) NOT NULL)
GO
INSERT INTO dbo.Updates (BeverageID, BeverageName)
VALUES	(1, 'Ground Coffee'),
		(2, 'English Breakfast Tea'),
		(3, 'Water'),
		(5, 'Hot Chocolate')
GO
SELECT 'Before' AS Description, BeverageID, BeverageName, DateAdded, DateUpdated FROM dbo.Beverages;
SELECT BeverageID, BeverageName FROM dbo.Updates;
GO

Now here is the Merge statement in action:

--Synchronise the target table (Beverages) with the refreshed data from the source table (Updates)
MERGE dbo.Beverages AS TARGET
USING dbo.Updates AS SOURCE ON (TARGET.BeverageID = SOURCE.BeverageID)
--When the records are matched on Beverage ID but the name is different, update the record, also setting the DateUpdated field
WHEN MATCHED AND TARGET.BeverageName <> SOURCE.BeverageName
THEN
    UPDATE SET TARGET.BeverageName = SOURCE.BeverageName,
               TARGET.DateUpdated = GETDATE()
--When new Beverage records are received and don`t match the target, insert the new records and set the DateAdded field
WHEN NOT MATCHED BY TARGET
THEN
    INSERT (BeverageID, BeverageName, DateAdded)
    VALUES (SOURCE.BeverageID, SOURCE.BeverageName, GETDATE())
--When a Beverage exists in the target but no update record has been received, we assume it has been deleted, so delete it from the Beverages table
WHEN NOT MATCHED BY SOURCE 
THEN
    DELETE
--The $action field (which is type nvarchar(10)) in the OUTPUT clause returns one of three values for each row: 'INSERT', 'UPDATE', or 'DELETE', 
--according to the action that has been performed on that row
OUTPUT $action,
       DELETED.BeverageID AS TargetBeverageID, DELETED.BeverageName AS TargetBeverageName,
       INSERTED.BeverageID AS SourceBeverageID, INSERTED.BeverageName AS SourceBeverageName;
GO

Now check the Beverages table after the changes:

SELECT 'After' AS Description, BeverageID, BeverageName, DateAdded, DateUpdated FROM dbo.Beverages
GO

Script to list the recovery models for every database

Below is a script that can be used to retrieve the recovery model for each database:

Please ensure you test any script taken from my website on a test/development machine, before running on a production server

CREATE TABLE #DBs (DBName VARCHAR(100), RecoveryModel SQL_VARIANT)
INSERT INTO #DBs 
EXEC sp_msforeachdb 'SELECT ''?'', DATABASEPROPERTYEX(''?'', N''RECOVERY'')'
SELECT * FROM #DBs
DROP TABLE #DBs

SSMS can’t see network locations

I was having problems with SSMS (SQL Server Management Studio) 2016 which I have been running with administrator permissions, whereby I couldn’t open files on my mapped network drives.

Doing a bit of Google’ing, I came across this article – https://technet.microsoft.com/en-us/library/ee844140(v=WS.10).aspx

I applied the registry fix:

To configure the EnableLinkedConnections registry value

1. Click Start, type regedit in the Start programs and files box, and then press ENTER.
2. Locate and then right-click the registry subkey HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindowsCurrentVersionPoliciesSystem.
3. Point to New, and then click DWORD Value.
4. Type EnableLinkedConnections, and then press ENTER.
5. Right-click EnableLinkedConnections, and then click Modify.
6. In the Value data box, type 1, and then click OK.
7. Exit Registry Editor, and then restart the computer.

And, it has fixed the problem 🙂

Script to archive offline databases

Below is the script that can be used to process the offline databases:

• Bring database online
• Backup the database
• Detach the database
• Move the MDF and LDF file(s)

The first part to bring the databases online, back them up, and detach them, is:

Please ensure you test any script taken from my website on a test/development machine, before running on a production server

USE master
GO

DECLARE @backuplocation VARCHAR(500)
SET @backuplocation = 'E:\SQLBackups\'

CREATE TABLE TempFilesToMove
( filename VARCHAR(500)
)

DECLARE csr CURSOR FAST_FORWARD FOR
    SELECT name FROM sysdatabases
    WHERE (status & 512) = 512

DECLARE @name VARCHAR(255)
DECLARE @sql VARCHAR(1000)

OPEN csr
FETCH NEXT FROM csr INTO @name

IF @backuplocation not like '%\' SET @backuplocation = @backuplocation + '\'

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'ALTER DATABASE [' + @name + '] SET ONLINE'
    EXEC (@sql)
    SET @sql = 'BACKUP DATABASE [' + @name + '] TO DISK = N' + CHAR(39) + @backuplocation + @name + '.bak' + CHAR(39)
    EXEC (@sql)
    SET @sql = 'INSERT INTO TempFilesToMove (filename) SELECT filename FROM [' + @name + '].dbo.sysfiles'
    EXEC (@sql)
    EXEC sp_detach_db @name
    FETCH NEXT FROM csr INTO @name
END

CLOSE csr
DEALLOCATE csr

Please ensure you take a copy of the data in TempFilesToMove using the statement below to make sure you have a list of where the files are:

SELECT * FROM TempFilesToMove

The second part to move the MDF and LDF files, is:

DECLARE @backuplocation VARCHAR(500)
SET @backuplocation = 'E:\SQLBackups\'

DECLARE csr2 CURSOR FAST_FORWARD FOR 
    SELECT filename FROM TempFilesToMove 

DECLARE @filename VARCHAR(500)
DECLARE @cmd VARCHAR(500)
OPEN csr2
FETCH NEXT FROM csr2 INTO @filename

EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE

WHILE @@FETCH_STATUS = 0
BEGIN
    
    SET @cmd = 'MOVE ' + @filename + ' ' + @backuplocation    
    EXEC master..xp_cmdshell @cmd
    FETCH NEXT FROM csr2 INTO @filename
END

CLOSE csr2
DEALLOCATE csr2

DROP TABLE TempFilesToMove

If the service account that runs SQL Server doesn’t have necessary permissions to the archive location, then you may get Access Denied messages when you run the second part. That’s why I suggest taking a copy of the TempFilesToMove table, just in case you need to run it manually.

SQL: Revised list of table columns, with data types, keys, constraints, etc.

I have just improved the script in SQL: List of table columns, with data types:

Please ensure you test any script taken from my website on a test/development machine, before running on a production server

It now includes an indicate for nullable fields, primary and foreign keys, unique and check constraints, and identity columns

SELECT
      schema_name(O.schema_id) + '.' + O.name AS TableName,
      C.name AS ColumnName,
      T.name AS Type,
      C.max_length AS Length,
	  CASE WHEN c.is_nullable = 1 THEN 'YES' ELSE NULL END AS Nullable,
	  CASE WHEN P.ColumnName IS NOT NULL THEN 'YES' ELSE NULL END AS PrimaryKey,
	  CASE WHEN F.ReferencingColumN IS NOT NULL THEN 'YES' ELSE NULL END AS ForeignKey,
	  CASE WHEN U.ColumnName IS NOT NULL THEN 'YES' ELSE NULL END AS [Unique],
	  CC.Definition AS CheckConstraint,
	  CASE WHEN c.is_identity = 1 THEN 'YES' ELSE NULL END AS [Identity]
FROM        sys.objects O
INNER JOIN  sys.columns C ON O.object_id = C.object_id
INNER JOIN  sys.types T ON C.user_type_id = T.user_type_id
LEFT JOIN (
	SELECT p.name AS ParentTable, pc.name AS ParentColumn, r.name AS ReferencingTable, rc.name AS ReferencingColumn
	FROM sys.foreign_key_columns f
	JOIN sys.objects p ON f.parent_object_id = p.object_id
	JOIN sys.objects r ON f.referenced_object_id = r.object_id
	JOIN sys.columns pc ON f.parent_object_id = pc.object_id AND f.parent_column_id = pc.column_id
	JOIN sys.columns rc ON f.referenced_object_id = rc.object_id AND f.referenced_column_id = rc.column_id
) AS F ON O.name = F.ParentTable AND C.name = F.ParentColumn
LEFT JOIN(
	SELECT i.name AS IndexName, OBJECT_NAME(ic.OBJECT_ID) AS TableName, COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
	FROM sys.indexes AS i 
	JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
	WHERE i.is_primary_key = 1
) AS P ON O.name = P.TableName AND C.name = P.ColumnName
LEFT JOIN(
	SELECT i.name AS IndexName, OBJECT_NAME(ic.OBJECT_ID) AS TableName, COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
	FROM sys.indexes AS i 
	JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
	WHERE i.is_unique_constraint = 1
) AS U ON O.name = U.TableName AND C.name = U.ColumnName
LEFT JOIN(
	SELECT co.name AS TableName, c.name AS ColumnName, definition 
	FROM sys.check_constraints cc
	JOIN sys.objects co ON cc.parent_object_id = co.object_id
	JOIN sys.columns c ON co.object_id = c.object_id AND cc.parent_column_id = c.column_id
) AS CC ON O.name = CC.TableName AND C.name = CC.ColumnName
WHERE O.type = 'U'
ORDER BY
      schema_name(O.schema_id),
      O.name,
      C.column_id
GO

Question: SQL Query

Back in July 2010, I was asked the following question by Pramod:

Is it possible to get a Pivot Table kind of an output as a result of a query. For example I have a table like this

Id

Date

Sales

Region

and as an output I want the a list of region wise sales with a monthly breakup and the respective sales.

Can you help?

My suggestion was to use the query below:

SELECT
  Region, [1] AS Jan2010, [2] AS Feb2010,
          [3] AS Mar2010, [4] AS Apr2010, 
          [5] AS May2010, [6] AS June2010, 
          [7] AS July2010
FROM
(  SELECT Region, Sales, 
          MONTH([Date]) AS SalesMonth
   FROM Sales) S
PIVOT
(
  SUM (Sales)
  FOR SalesMonth IN
    ( [1], [2], [3], [4], [5], [6], [7] )
  ) AS pvt
ORDER BY pvt.Region

This assumed at the time that all the data was for 2010.

Product Category Hierarchy (with Breadcrumb Trail) using CTE query

The example query below creates a product category table and populates it with sample data. I then use a CTE query to return the categories, along with the breadcrumb trail (lineage)

CREATE TABLE dbo.ProductCategory
( ID INT NOT NULL PRIMARY KEY,
  Category VARCHAR(100) NOT NULL,
  ParentID INT NULL)
GO

INSERT INTO dbo.ProductCategory
	(ID, Category, ParentID)
VALUES	(1, 'All Products', NULL),
	(2, 'Cars', 1), (3, 'Vauxhall', 2),
	(4, 'Corsa', 3), (5, 'Astra', 3),
	(6, 'Ford', 2), (7, 'Fiesta', 6),
	(8, 'Ka', 6), (9, 'Mondea', 6),
	(10, 'BMW', 2), (11, 'Motorbikes', 1),
	(12, 'Kawasaki', 11), (13, 'Suzuki', 11)
GO

WITH CTE_ProductCategories 
	(ID, Category, ParentID, Level, Lineage)
AS (
	SELECT ID, Category, ParentID, 0 AS Level, 
		CAST ([Category] AS VARCHAR (MAX))
			AS [Lineage]
	FROM dbo.ProductCategory
	WHERE ParentID IS NULL
	UNION ALL
	SELECT pc.ID, pc.Category, pc.ParentID, 
		p1.Level + 1, 
		p1.Lineage + ' -> ' 
		+ CAST ([pc].[Category] AS VARCHAR (MAX))
	FROM dbo.ProductCategory AS pc
	JOIN CTE_ProductCategories AS p1 
		ON p1.ID = pc.ParentID
)
SELECT ID, Category, ParentID, Level, Lineage
FROM CTE_ProductCategories
ORDER BY ParentID, Category
GO


Results:

ID	Category	ParentID	Level	Lineage
1	All Products	NULL	0	All Products
2	Cars	1	1	All Products -> Cars
11	Motorbikes	1	1	All Products -> Motorbikes
10	BMW	2	2	All Products -> Cars -> BMW
6	Ford	2	2	All Products -> Cars -> Ford
3	Vauxhall	2	2	All Products -> Cars -> Vauxhall
5	Astra	3	3	All Products -> Cars -> Vauxhall -> Astra
4	Corsa	3	3	All Products -> Cars -> Vauxhall -> Corsa
7	Fiesta	6	3	All Products -> Cars -> Ford -> Fiesta
8	Ka	6	3	All Products -> Cars -> Ford -> Ka
9	Mondea	6	3	All Products -> Cars -> Ford -> Mondea
12	Kawasaki	11	2	All Products -> Motorbikes -> Kawasaki
13	Suzuki	11	2	All Products -> Motorbikes -> Suzuki

SQL: CONVERT style examples when converting Date/Time to String

The script below can be used to obtain an example of each of the different styles that can be output using the CONVERT Transact-SQL statement. I have output the styles including the century, but the script can easily be modified to output examples without.

DECLARE @Date DATETIME
SET @Date = GETDATE()

SELECT '100' AS [CONVERT style], 'Default' AS Standard, 'mon dd yyyy hh:miAM (or PM)' AS Format, CONVERT(VARCHAR(100), @Date, 100) AS Example
UNION SELECT '101', 'U.S.', 'mm/dd/yyyy', CONVERT(VARCHAR(100), @Date, 101)
UNION SELECT '102', 'ANSI', 'yyyy.mm.dd', CONVERT(VARCHAR(100), @Date, 102)
UNION SELECT '103', 'British/French', 'dd/mm/yyyy', CONVERT(VARCHAR(100), @Date, 103)
UNION SELECT '104', 'German', 'dd.mm.yyyy', CONVERT(VARCHAR(100), @Date, 104)
UNION SELECT '105', 'Italian', 'dd-mm-yyyy', CONVERT(VARCHAR(100), @Date, 105)
UNION SELECT '106', '', 'dd mon yyyy', CONVERT(VARCHAR(100), @Date, 106)
UNION SELECT '107', '', 'Mon dd, yyyy', CONVERT(VARCHAR(100), @Date, 107)
UNION SELECT '108', '', 'hh:mi:ss', CONVERT(VARCHAR(100), @Date, 108)
UNION SELECT '109', 'Default + ms', 'mon dd yyyy hh:mi:ss:mmmAM (or PM)', CONVERT(VARCHAR(100), @Date, 109)
UNION SELECT '110', 'USA', 'mm-dd-yyyy', CONVERT(VARCHAR(100), @Date, 110)
UNION SELECT '111', 'Japan', 'yyyy/mm/dd', CONVERT(VARCHAR(100), @Date, 111)
UNION SELECT '112', 'ISO', 'yyyymmdd', CONVERT(VARCHAR(100), @Date, 112)
UNION SELECT '113', 'Europe default + ms', 'dd mon yyyy hh:mi:ss:mmm(24h)', CONVERT(VARCHAR(100), @Date, 113)
UNION SELECT '114', '', 'hh:mi:ss:mmm(24h)', CONVERT(VARCHAR(100), @Date, 114)
UNION SELECT '120', 'ODBC canonical', 'yyyy-mm-dd hh:mi:ss(24h)', CONVERT(VARCHAR(100), @Date, 120)
UNION SELECT '121', 'ODBC canonical + ms', 'yyyy-mm-dd hh:mi:ss.mmm(24h)', CONVERT(VARCHAR(100), @Date, 121)
UNION SELECT '126', 'ISO 8601', 'yyyy-mm-ddThh:mi:ss.mmm (no spaces)', CONVERT(VARCHAR(100), @Date, 126)


An example output that I have just run is:

CONVERT style	Standard	Format	Example
100	Default	mon dd yyyy hh:miAM (or PM)	Jun 16 2015 11:43AM
101	U.S.	mm/dd/yyyy	06/16/2015
102	ANSI	yyyy.mm.dd	2015.06.16
103	British/French	dd/mm/yyyy	16/06/2015
104	German	dd.mm.yyyy	16.06.2015
105	Italian	dd-mm-yyyy	16-06-2015
106		dd mon yyyy	16 Jun 2015
107		Mon dd, yyyy	Jun 16, 2015
108		hh:mi:ss	11:43:59
109	Default + ms	mon dd yyyy hh:mi:ss:mmmAM (or PM)	Jun 16 2015 11:43:59:457AM
110	USA	mm-dd-yyyy	06-16-2015
111	Japan	yyyy/mm/dd	2015/06/16
112	ISO	yyyymmdd	20150616
113	Europe default + ms	dd mon yyyy hh:mi:ss:mmm(24h)	16 Jun 2015 11:43:59:457
114		hh:mi:ss:mmm(24h)	11:43:59:457
120	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)	2015-06-16 11:43:59
121	ODBC canonical + ms	yyyy-mm-dd hh:mi:ss.mmm(24h)	2015-06-16 11:43:59.457
126	ISO 8601	yyyy-mm-ddThh:mi:ss.mmm (no spaces)	2015-06-16T11:43:59.457

SQL: Count of email domain names

The script below, returns the number of records grouped by the domain name of each email address (i.e. to the right of the @ sign).

SELECT
  RIGHT(Email, LEN(EMAIL) - CHARINDEX('@', EMAIL)) AS EmailDomain,
  COUNT(*) EmailCount
FROM Users S
GROUP BY RIGHT(EMAIL, LEN(EMAIL) - CHARINDEX('@', EMAIL))
ORDER BY EmailCount DESC