SQL: Number of records in all views

I wanted to count the number of records in each view within the current database, so have developed the script below:

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

DECLARE vwCursor CURSOR FAST_FORWARD FOR
  SELECT SCHEMA_NAME(schema_id) AS schema_name,
         name as view_name FROM sys.views
  ORDER BY name

OPEN vwCursor

DECLARE @SchemaName NVARCHAR(100)
DECLARE @ViewName NVARCHAR(100)
DECLARE @SQL NVARCHAR(MAX)

CREATE TABLE #Results
( ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  SchemaName VARCHAR(100),
  ViewName VARCHAR(100),
  RecordCount INT
)

FETCH NEXT FROM vwCursor INTO @SchemaName, @ViewName

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @SQL = 'INSERT INTO #Results '
  SET @SQL = @SQL + '(SchemaName, ViewName, RecordCount) '
  SET @SQL = @SQL + 'SELECT ' + CHAR(39) + @SchemaName + CHAR(39) + ' AS SchemaName, '
  SET @SQL = @SQL + CHAR(39) + @ViewName + CHAR(39) + ' AS ViewName, COUNT(*) '
  SET @SQL = @SQL + 'FROM [' + @SchemaName + '].[' + @ViewName + ']'
  EXEC sys.sp_executesql @statement = @SQL

  FETCH NEXT FROM vwCursor INTO @SchemaName, @ViewName
END
CLOSE vwCursor
DEALLOCATE vwCursor

SELECT * FROM #Results

DROP TABLE #Results

Right-align numeric values in SSMS grid

Thanks to the post from Daniel Hutmacher ( https://sqlsunday.com/2018/06/12/right-align-columns-in-ssms/ ) I have found a great way to right-align numeric values in SQL Server Management Studio when viewing results in a grid.

CREATE TABLE #Numbers
( Number FLOAT
)
INSERT INTO #Numbers (Number)
VALUES (1), (1.5), (100.75), (1234.56)

SELECT
  Number,
  REPLACE(STR(Number, 15, 2), ' ', '  ')
    AS RightAlignedNumber
FROM #Numbers

DROP TABLE #Numbers

SQL: ASCII characters code for each character of a field (all records)

I wanted to look for all non-standard (i.e. A-Z/a-z/0-9) characters in a field across all records in a table. Therefore, I have adjusted my previous script to do so:

SET NOCOUNT ON;
CREATE TABLE #Characters
( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  RecordID INT NOT NULL,
  [Character] NCHAR(1),
  [ASCIIValue] INT
)
DECLARE @RecordID INT;
DECLARE @counter int = 1;
DECLARE @colString NVARCHAR(MAX);
DECLARE search CURSOR FAST_FORWARD FOR
	SELECT RecordID, TextString
	FROM tableName WHERE ISNULL(TextString, '') <> ''
OPEN search
FETCH NEXT FROM search INTO @RecordID, @colString
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @counter = 1
	WHILE @counter <= DATALENGTH(@colString)
	BEGIN
		INSERT INTO #Characters (RecordID, [Character], [ASCIIValue])
		SELECT @RecordID,
			CHAR(ASCII(SUBSTRING(@colString, @counter, 1))) as [Character],
			ASCII(SUBSTRING(@colString, @counter, 1)) as [ASCIIValue]
		SET @counter = @counter + 1
	END
	FETCH NEXT FROM search INTO @RecordID, @colString
END
CLOSE search
DEALLOCATE search
SELECT * FROM #Characters
WHERE (ASCIIValue < 48 OR (ASCIIValue > 57 -- 0-9
  AND ASCIIValue < 65) OR (ASCIIValue > 90 -- A-Z
  AND ASCIIValue < 97) OR ASCIIValue > 122) -- a-z
ORDER BY ASCIIValue
DROP TABLE #Characters
SET NOCOUNT OFF
GO

Simply change to the required database, and change the select statement in the cursor.

Please always test on a development server/database before running on Production.

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

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:

IDCategoryParentIDLevelLineage
1All ProductsNULL0All Products
2Cars11All Products -> Cars
11Motorbikes11All Products -> Motorbikes
10BMW22All Products -> Cars -> BMW
6Ford22All Products -> Cars -> Ford
3Vauxhall22All Products -> Cars -> Vauxhall
5Astra33All Products -> Cars -> Vauxhall -> Astra
4Corsa33All Products -> Cars -> Vauxhall -> Corsa
7Fiesta63All Products -> Cars -> Ford -> Fiesta
8Ka63All Products -> Cars -> Ford -> Ka
9Mondea63All Products -> Cars -> Ford -> Mondea
12Kawasaki112All Products -> Motorbikes -> Kawasaki
13Suzuki112All Products -> Motorbikes -> Suzuki