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

Regular Expressions

I use Emurasoft’s EmEditor as my preferred text editor, and wanted to find a way to identify non alphanumeric characters, including foreign characters.

After a bit of Googling, I found a regular expression to find characters in a range.

The regular expression below is used to find any characters betwen 0 and 127, which includes A-Z, a-z, 0-9, various symbols.

Plus 192 to 255, such as À to ÿ.

[x00-x7FxC0-xFF]

Azure: Create new login

Within the Master database:

CREATE LOGIN [username] WITH password='password';

Within the database itself:

CREATE USER [username] FROM LOGIN [username];
ALTER ROLE db_datareader ADD MEMBER [username];

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.

Excel: Add leading zeroes to a number

I wanted to add leading zeroes to a number column, making a seven-digit number. I found the REPT function:

=IF(LEN(A2)>7,A2,REPT("0",7-LEN(A2))&A2)

If the cell is longer than 7 digits, it puts the whole cell value in. If it is 7 or less, it will “repeat” the zero the required number of times.

Hope this helps 🙂

MVC Bootstrap Update breaks CSS

Hi.

I recently updated all of the NuGet packages in one of my solutions, and it seemed to break the CSS.

I recreated the project and copied the model, controller and views across to the new project. I then updated the NuGet packages one by one, and when I updated to Bootstrap 4.0.0 (or later) it broke!

When I downgraded to 3.3.7 it all came back again 🙂

Therefore, it is probably outdated code in the MVC template in Visual Studio that causes the new bootstrap to break it.

Hopefully this will help other people having the same issue

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