Category Archives: T-SQL

How to generate a random password with T-SQL

The following script will generate a random 10 character password that meets the complexity requirement for Microsoft Windows. To generate a password just run the script in a new SQL Server Management Studio window. The logic can also be easily turned into a function.

The option of symbol characters is limited to what’s shown below as dealing with quotes and obscure characters in a password is often more trouble than it is worth. The password generated however should still be very secure as it will be 10 characters long with a guaranteed number, lowercase letter, uppercase letter and a symbol.

!
#
$
%
&
(
)
*
+

/*Declare Variables*/
DECLARE @i INT;
DECLARE @Pw VARCHAR(MAX);
DECLARE @Numbers TABLE (Characters CHAR(1));
DECLARE @LowerCase TABLE (Characters CHAR(1));
DECLARE @UpperCase TABLE (Characters CHAR(1));
DECLARE @Symbols TABLE (Characters CHAR(1));
DECLARE @BaseCharacters TABLE (Characters CHAR(1));
DECLARE @GuaranteedCharacters TABLE (Characters CHAR(1));
DECLARE @PwCharacters TABLE (Characters CHAR(1));

/*Generate Numbers*/
SET @i = 0;

WHILE @i <= 9
BEGIN
	INSERT INTO @Numbers
	SELECT @i

	SET @i = @i + 1
END;

/*Generate Lowercase Letters*/
SET @i = 97;

WHILE @i <= 122
BEGIN
	INSERT INTO @LowerCase
	SELECT CHAR(@i)

	SET @i = @i + 1
END;

/*Generate Uppercase Letters*/
SET @i = 65;

WHILE @i <= 90
BEGIN
	INSERT INTO @UpperCase
	SELECT CHAR(@i)

	SET @i = @i + 1
END;

/*Generate Symbols*/
SET @i = 33;

WHILE @i <= 43
BEGIN
	IF (
			@i = 34
			OR @i = 39
			)
	BEGIN
		SET @i = @i + 1
	END

	INSERT INTO @Symbols
	SELECT CHAR(@i)

	SET @i = @i + 1
END;

/*
Randomly Select A Number, Lowercase Letter,
Uppercase Letter And A Symbol So Four Character Types
Are Guaranteed To Be Present Somewhere In The Password
*/
INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @Numbers
ORDER BY NEWID();

INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @LowerCase
ORDER BY NEWID();

INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @UpperCase
ORDER BY NEWID();

INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @Symbols
ORDER BY NEWID();

/*
Randomly Select Another 6 Characters
*/
INSERT INTO @BaseCharacters
SELECT TOP 6 Characters
FROM (
	SELECT Characters
	FROM @Numbers
	
	UNION ALL
	
	SELECT Characters
	FROM @LowerCase
	
	UNION ALL
	
	SELECT Characters
	FROM @UpperCase
	
	UNION ALL
	
	SELECT Characters
	FROM @Symbols
	) AS Characters
ORDER BY NEWID()

/*Generate A 10 Character Password*/
INSERT INTO @PwCharacters (Characters)
SELECT Characters
FROM (
	SELECT Characters
	FROM @BaseCharacters
	
	UNION ALL
	
	SELECT Characters
	FROM @GuaranteedCharacters
	) AS Characters
ORDER BY NEWID()

/*Save The Password To A String*/
SELECT @Pw = COALESCE(@Pw + Characters, Characters)
FROM @PwCharacters

SELECT @Pw AS PW;

 

If you found this post helpful please like, comment and share.

How to move and or rename Database files in SQL Server

An example use case for the process below could be you need to move database files to a new drive. Another example might be your organisation intends to run a legacy database along side a new updated database with both sharing the same database name in the same instance with the files located in the same directory with the same names. Obviously this cannot be done and requires the database names to differ and the files to be renamed or not exist in the same directory.

For example AdventureWorks might become AdventureWorks_Legacy while a new and improved AdventureWorks database retains the original database name. The associated database file names would also need to be changed/moved to reflect this.

Someone might also want to do something like this for test purposes but obviously having test resources in a live environment would not be recommended if avoidable.

The first step to moving and renaming the files is to copy and modify the script below. Note the script below assumes you want to move and change the names of the files. To avoid any database conflicts you only need to do one or the other.

/* 
Find & Replace DbName with the name of the Database you are working with
*/
USE [DbName];

/*
Changing Physical names and paths
Replace 'C:\...\NewDbName.mdf' with full path of new Db file to be used
*/
ALTER DATABASE DbName MODIFY FILE (
	NAME = ' DbName '
	,FILENAME = 'C:\...\NewDbName.mdf'
	);

/*
Replace 'C:\...\NewDbName_log.ldf' with full path of new Db log file to be used
*/
ALTER DATABASE DbName MODIFY FILE (
	NAME = ' DbName _log'
	,FILENAME = 'C:\...\NewDbName_log.ldf'
	);

/*
Changing logical names
*/
ALTER DATABASE DbName MODIFY FILE (
	NAME = DbName
	,NEWNAME = NewDbName
	);

ALTER DATABASE DbName MODIFY FILE (
	NAME = DbName_log
	,NEWNAME = NewDbName_log
	);
Once the script has been set up as desired follow the steps below:
  1. Open Microsoft SQL Server Management Studio (SSMS).
  2. Connect to the server that houses the Db you are working with.
  3. Run the modified script
  4. Right click on the Db in SSMS and select Tasks > Take Offline
  5. If you are moving the database files log into the server that houses the database files and copy and move the MDF and LDF files to the location you specified in first two alter commands. If the script specifies new names rename the copied files to match the names given in the script exactly.
  6. Go back to SSMS and right click on the Db and select Tasks > Bring Online.
  7. If you have moved the files once the database is back online and confirmed working as expected the unused original files can be deleted.
  8. Now you can rename the Db to the new name if you wish using SSMS.

How to demonstrate the space usage of a Null Varchar(Max) column

An empty Varchar(Max) column uses a negligible amount of disk space. The script below demonstrates this by creating the table TestTb which contains one column named NullColumn that has a Varchar(Max) data type. When the table is created the column NullColumn is populated with 100,000 rows of Null.

The two readings below show the table when it has just been created and the table with 100,000 rows entered.

Results

IF OBJECT_ID('dbo.TestTb', 'U') IS NOT NULL
	DROP TABLE dbo.TestTb;

CREATE TABLE TestTb (NullColumn VARCHAR(MAX));
GO

sp_spaceused 'TestTb';

DECLARE @i AS INT;

SET @i = 0;

WHILE @i < 100000
BEGIN
	INSERT INTO TestTb (NullColumn)
	VALUES (NULL)

	SET @i = @i + 1
END;
GO

sp_spaceused 'TestTb';

IF OBJECT_ID('dbo.TestTb', 'U') IS NOT NULL
	DROP TABLE dbo.TestTb;

 

How to filter sp_who2 to create KILL statements

Until now the only way to kill wayward commands or queries in SQL Server was to run sp_who or sp_who2, look for the record with abnormal CpuTime or DiskIO readings (or look for the login of the guy who never knows what he’s doing), and take note of the corresponding Spid number by running your finger along the screen tracing it back to the ID so you don’t get confused and end up killing the wrong Spid from another record by mistake.

But now there’s a better way if you know the the likely culprit you want to kill in advance. The script below will allow you to filter the results of sp_who2 based on any of the returned columns. You’ll now be able to specify the database name or login name etc. and the query will return only the rows that match your constraints. Narrow the results down enough and you’ll be left with one record to kill. Then copy the results of the KillSpid column and paste to a new SSMS window. Now you should be left with one Kill command to run with no possibility of killing the wrong Spid.

IF OBJECT_ID('tempdb..#sp_who2') IS NOT NULL DROP TABLE #sp_who2
GO

CREATE TABLE #sp_who2 (
	Spid INT
	,Status VARCHAR(255)
	,LoginName VARCHAR(255)
	,HostName VARCHAR(255)
	,BlkBy VARCHAR(255)
	,DbName VARCHAR(255)
	,Command VARCHAR(255)
	,CpuTime INT
	,DiskIO INT
	,LastBatch VARCHAR(255)
	,ProgramName VARCHAR(255)
	,Spid2 INT
	,RequestId INT
	)

INSERT INTO #sp_who2
EXEC sp_who2

SELECT 'Kill ' + CONVERT(VARCHAR(MAX), SPID) AS KillSpid
	,Spid 
	,Status 
	,LoginName 
	,HostName 
	,BlkBy 
	,DbName 
	,Command 
	,CpuTime 
	,DiskIO 
	,LastBatch 
	,ProgramName 
	,Spid2
	,RequestId
FROM #sp_who2
-- Add any filtering of the results here :
WHERE DBName NOT IN ('master')
-- Add any sorting of the results here :
-- AND ProgramName = ''
-- AND DbName = ''
-- AND LoginName = ''
-- AND HostName = ''
-- AND Status = ''
ORDER BY Spid ASC
,DBName ASC;

DROP TABLE #sp_who2

 

How to determine why a T-SQL command is unreasonably slow

If you’ve ever found yourself in the situation were a command executing against a small table is nowhere near instant there can be numerous reasons for this but the most common causes are locks and waits.

The first step in identifying the problem is to execute the script below in a new query window while the troublesome command is running.

/* Queries Not Running */
SELECT ROW_NUMBER() OVER (
		ORDER BY r.total_elapsed_time DESC
		) AS Rn
	,st.TEXT AS SqlText
	,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.status <> 'running';

/* Queries Running */
SELECT ROW_NUMBER() OVER (
		ORDER BY r.total_elapsed_time DESC
		) AS Rn
	,st.TEXT AS SqlText
	,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.status = 'running';

 

This script will return two lists of the currently active sessions along with the stats associated with their execution. The first list will contain all the active sessions that are not running. The second list will contain all the active sessions that are running and will likely not contain the troublesome query you’re dealing with.

Identify your session based on the SqlText field. Be sure you’ve identified the session correctly as you may decide you want to kill the process later and killing the wrong one could cause you a lot of trouble.

  • status : If the status is not running look to the other fields in the returned result set to help identify the problem. If the session is in the running result set but you are unhappy with the performance it is likely the T-SQL needs to be optimized to make it run faster. This is a very broad topic and there are tons of articles and guides on the internet dealing with it.
  • blocking_session_id : If another session is blocking yours from executing, e.g. it has locked a table your command needs to write to, then this field will include the Id of the session causing the table to be locked. You can use EXEC sp_who2 to assess if the underlying command/query is experiencing a problem. If you are familiar with the blocking session you may know that you are able to kill the session without incurring any negative consequences. You can use the following code snippet to kill the blocking session.
    KILL blocking_session_id /*replace by the actual Id*/

    NOTE: Before you kill anything if it’s a command that has been running for a very long time it will likely take at least the same amount of time to roll back and unlock the table. You might be better off waiting for the session to finish on its own.

  • wait_type : If no blocking session is available, then the query is waiting for something, e.g. server resources etc. More details about wait types can be found HERE
  • wait_time : This stat value is measured in milliseconds. Short wait times are fine, specially in PAGEIOLATCH wait types (access to physical files) but longer wait times indicate a more serious problem.
  • last_wait_type : Indicates if the last wait type was different. This is quite helpful in analyzing if the query was blocked for the same reason before.

 

How to tell if you are a member of a SQL Server group or create a list of group members using T-SQL

The following scripts will help you determine if you are a member of a group or role or create a list of group members in SQL Server without having to use SQL Server Management Studio. This is a particularly handy script in determining who might have access to the server through Active Directory groups.

/*
The code below indicates whether the current user is a member 
of the specified Microsoft Windows group or SQL Server database role.
A result of 1 = yes
,0 = no
,null = the group or role queried is not valid.
*/

SELECT IS_MEMBER('group or role')

/* 
Example. 
*/

SELECT IS_MEMBER('db_owner')

/*
The code below will create a list of all the logins that are members 
of a group. Don't forget to include domain, e.g. admin\user
*/

EXEC master..xp_logininfo 
@acctname = [group],
@option = 'members'

 

How to remove Logins and Users from SQL Server en masse

The script below will create SQL code to drop every user and login from a SQL Server instance. Not very useful unless you’re trying to give someone, possibly yourself, a very bad day. Don’t worry though the script does not execute the code, to reiterate it only generates it. However given the potential for you using the output of the script incorrectly this post comes with a disclaimer (Link to disclaimer).

To make the script a little more useful you can populate a temp table called #TheseUsersOnly in the script with the specific login/user names you want to remove.

This will limit the code outputted to only the logins and users specified.

Go to the /*INSERT LOGINS HERE*/ section to populate the temp table with hard coded login/user names.

Be sure to thoroughly review the code outputted before executing it.

If you encounter this error message “The database principal owns a schema in the database, and cannot be dropped“, see this post (Link).

SET NOCOUNT ON;

/*DECLARE VARIABLES*/
DECLARE @Database TABLE (DbName SYSNAME);
DECLARE @DbName AS SYSNAME;
DECLARE @sqlCommand AS VARCHAR(MAX);
DECLARE @UserName AS VARCHAR(128);
DECLARE @i AS INT;
DECLARE @z AS INT;
DECLARE @j AS INT;
DECLARE @y AS INT;

/*DROP EXISTING TEMP TABLES*/
IF OBJECT_ID('tempdb..#ErrorTable') IS NOT NULL
BEGIN
	DROP TABLE #ErrorTable
END;

IF OBJECT_ID(N'tempdb..#User') IS NOT NULL
BEGIN
	DROP TABLE #User
END;

IF OBJECT_ID(N'tempdb..#UserOrdered') IS NOT NULL
BEGIN
	DROP TABLE #UserOrdered
END;

IF OBJECT_ID(N'tempdb..#TheseUsersOnly') IS NOT NULL
BEGIN
	DROP TABLE #TheseUsersOnly
END;

IF OBJECT_ID('tempdb..#Return') IS NOT NULL
BEGIN
	DROP TABLE #Return
END;

/*CREATE TEMP TABLES*/
/*THE ERROR TABLE WILL STORE THE DYNAMIC SQL THAT DID NOT WORK*/
CREATE TABLE [#ErrorTable] ([SqlCommand] VARCHAR(MAX)) ON [PRIMARY];

CREATE TABLE #User (
	Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
	,DbName SYSNAME NULL
	,UserName SYSNAME NULL
	);

CREATE TABLE #UserOrdered (
	Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
	,DbName SYSNAME NULL
	,UserName SYSNAME NULL
	);

CREATE TABLE #TheseUsersOnly (UserName SYSNAME NULL);

CREATE TABLE [#Return] ([SqlCommand] VARCHAR(MAX)) ON [PRIMARY];

/*INSERT LOGINS HERE*/
/*
/*Single Login*/
INSERT INTO #TheseUsersOnly (UserName)
SELECT 'Test_Login';
*/
/*
/*Multiple Logins*/
INSERT INTO #TheseUsersOnly (UserName)
VALUES (Test_Login_1)
,(Test_Login_2)
,(Test_Login_3)
*/
SET @DbName = '';

/*GENERATE LIST TABLE OF DATABASE NAMES*/
INSERT INTO @Database (DbName)
SELECT NAME
FROM master.dbo.sysdatabases
WHERE NAME <> 'tempdb'
ORDER BY NAME ASC;

/*GENERATE LIST OF USERS FOR EACH DATABASE*/
WHILE @DbName IS NOT NULL
BEGIN
	SET @DbName = (
			SELECT MIN(DbName)
			FROM @Database
			WHERE DbName > @DbName
			)
	SET @sqlCommand = '
INSERT INTO #User (
DbName
,UserName
)
SELECT ' + '''' + @DbName + '''' + ' AS DbName 
,princ.name AS UserName
FROM ' + QUOTENAME(@DbName) + '.sys.database_principals AS princ
WHERE princ.name IS NOT NULL
';

	/*OPTION TO LIMIT USERS TO ONLY THE USERS SPECIFIED*/
	IF EXISTS (
			SELECT *
			FROM #TheseUsersOnly
			)
	BEGIN
		SET @sqlCommand = @sqlCommand + 'AND princ.Name IN (SELECT UserName FROM #TheseUsersOnly);'
	END
	ELSE
	BEGIN
		SET @sqlCommand = @sqlCommand + ';'
	END

	/*ERROR HANDLING*/
	BEGIN TRY
		EXEC (@sqlCommand)
	END TRY

	BEGIN CATCH
		INSERT INTO #ErrorTable
		SELECT (@sqlCommand)
	END CATCH
END;

/*REORDER USER TABLE BY NAME ALPHABETICALLY ASCENDING*/
INSERT INTO #UserOrdered (
	DbName
	,UserName
	)
SELECT DbName
	,UserName
FROM #User
ORDER BY UserName ASC
	,DbName ASC;

/*SET LOOP VARIABLES*/
SET @y = 0;
SET @i = 1;
SET @z = (
		SELECT COUNT(*)
		FROM #UserOrdered
		);

/*LOOP TO GENERATE SQL CODE*/
WHILE @i <= @z
BEGIN
	SELECT @DbName = DbName
		,@UserName = UserName
	FROM #UserOrdered
	WHERE Id = @i

	IF @y = 0
		SET @j = 1;

	BEGIN
		SET @y = (
				SELECT COUNT(UserName)
				FROM #UserOrdered
				WHERE UserName = @UserName
				)
	END

	SET @sqlCommand = 'USE [' + @DbName + '];
IF EXISTS (SELECT * FROM ' + QUOTENAME(@DbName) + '.sys.database_principals WHERE name = N' + '''' + @UserName + '''' + ')
BEGIN DROP USER [' + @UserName + '] END;
';

	/*ERROR HANDLING*/
	BEGIN TRY
		INSERT INTO #Return
		SELECT (@sqlCommand)
	END TRY

	BEGIN CATCH
		INSERT INTO #ErrorTable
		SELECT (@sqlCommand)
	END CATCH

	IF @j = @y
	BEGIN
		IF EXISTS (
				SELECT NAME
				FROM master.sys.server_principals
				WHERE NAME = @UserName
				)
		BEGIN
			INSERT INTO #Return (SqlCommand)
			SELECT 'USE [master];
IF EXISTS (SELECT NAME
FROM master.sys.server_principals
WHERE NAME = ' + '''' + @UserName + '''' + '
) BEGIN
DROP LOGIN [' + @UserName + '] END;'
		END

		SET @y = 0;
	END

	SET @i = @i + 1;
	SET @j = @j + 1;
END

/*RESULTS*/
SELECT SqlCommand
FROM #ErrorTable;

SELECT SqlCommand
FROM #Return;

/*HOUSEKEEPING*/
IF OBJECT_ID('tempdb..#ErrorTable') IS NOT NULL
BEGIN
	DROP TABLE #ErrorTable
END;

IF OBJECT_ID(N'tempdb..#User') IS NOT NULL
BEGIN
	DROP TABLE #User
END;

IF OBJECT_ID(N'tempdb..#UserOrdered') IS NOT NULL
BEGIN
	DROP TABLE #UserOrdered
END;

IF OBJECT_ID(N'tempdb..#TheseUsersOnly') IS NOT NULL
BEGIN
	DROP TABLE #TheseUsersOnly
END;

IF OBJECT_ID('tempdb..#Return') IS NOT NULL
BEGIN
	DROP TABLE #Return
END;

 

How to assess a SQL Server instance for GDPR compliance by writing every table and column to Excel

This post uses the script I had written before here LINK modified slightly to include a count of each table. If you are a DBA you’re likely assisting compliance offers to find personal identifiable data within the databases at this time. This script will allow you to provide them with a record of every database, table and column on an entire instance. The compliance offer can then sieve through all the columns and highlight any columns that look like they contain personal data for further investigation.

Below is a SQL query that will return the following metadata about each table from each database located on a SQL server database server:

  • ServerName
  • DatabaseName
  • SchemaName
  • TableName
  • CountOfRows
  • ColumnName
  • KeyType

The output is provided in a table format with these additional formatted lines of text which can be used as queries or as part of queries.

  • A Select table query
  • A Count table row columns
  • A Select column query
  • Each column bracketed
  • Each table and column bracketed

The table returned by the query can be exported to excel. Using excels filter option applied to the columns of the table makes finding and selecting specific tables and columns very easy.

This process can be repeated for every SQL Server instance used by the business to generate a single mapped servers master excel file allowing the user to find any table or column available to the organization quickly.

Applying some colour coding like below adds to the ease of use.

Image of excel file with mapped database server structure

How to use:

Simply open SQL Server Management Studio and from object explorer right click on the server name and select new query. This will open a window set to the master database of the server. Copy and paste the SQL below into this SQL Server window and execute. When the query is finished you will have created the table above.

/*
SCRIPT UPDATED
20180316
*/

USE [master]
GO

/*DROP TEMP TABLES IF THEY EXIST*/
IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL
	DROP TABLE #DatabaseList;

IF OBJECT_ID('tempdb..#TableStructure') IS NOT NULL
	DROP TABLE #TableStructure;

IF OBJECT_ID('tempdb..#ErrorTable') IS NOT NULL
	DROP TABLE #ErrorTable;

IF OBJECT_ID('tempdb..#MappedServer') IS NOT NULL
	DROP TABLE #MappedServer;
	
IF OBJECT_ID('tempdb..#TableCount') IS NOT NULL
	DROP TABLE #TableCount;
	
IF OBJECT_ID('tempdb..#Count') IS NOT NULL
	DROP TABLE #Count;

DECLARE @ServerName AS SYSNAME
DECLARE @Count INT

SET @ServerName = @@SERVERNAME

CREATE TABLE #DatabaseList (
	Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
	,ServerName SYSNAME
	,DbName SYSNAME
	);

CREATE TABLE [#TableStructure] (
	[DbName] SYSNAME
	,[SchemaName] SYSNAME
	,[TableName] SYSNAME
	,[ColumnName] SYSNAME
	,[KeyType] CHAR(7)
	) ON [PRIMARY];
	
CREATE TABLE [#TableCount] (
	[Id_TableCount] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
	,[ServerName] SYSNAME
	,[DatabaseName] SYSNAME
	,[SchemaName] SYSNAME
	,[TableName] SYSNAME
	,[SQLCommand] VARCHAR(MAX)
	,[TableCount] INT
	);

CREATE TABLE #Count (ReturnedCount INT);

/*THE ERROR TABLE WILL STORE THE DYNAMIC SQL THAT DID NOT WORK*/
CREATE TABLE [#ErrorTable] ([SqlCommand] VARCHAR(MAX)) ON [PRIMARY];

/*
A LIST OF DISTINCT DATABASE NAMES IS CREATED
THESE TWO COLUMNS ARE STORED IN THE #DatabaseList TEMP TABLE
THIS TABLE IS USED IN A FOR LOOP TO GET EACH DATABASE NAME
*/
INSERT INTO #DatabaseList (
	ServerName
	,DbName
	)
SELECT @ServerName
	,NAME AS DbName
FROM master.dbo.sysdatabases WITH (NOLOCK)
WHERE NAME <> 'tempdb'
ORDER BY NAME ASC

/*VARIABLES ARE DECLARED FOR USE IN THE FOLLOWING FOR LOOP*/
DECLARE @sqlCommand AS VARCHAR(MAX)
DECLARE @DbName AS SYSNAME
DECLARE @i AS INT
DECLARE @z AS INT

SET @i = 1
SET @z = (
		SELECT COUNT(*) + 1
		FROM #DatabaseList
		)

/*WHILE 1 IS LESS THAN THE NUMBER OF DATABASE NAMES IN #DatabaseList*/
WHILE @i < @z
BEGIN
	/*GET NEW DATABASE NAME*/
	SET @DbName = (
			SELECT [DbName]
			FROM #DatabaseList
			WHERE Id = @i
			)
	/*CREATE DYNAMIC SQL TO GET EACH TABLE NAME AND COLUMN NAME FROM EACH DATABASE*/
	SET @sqlCommand = 'USE [' + @DbName + '];' + '

INSERT INTO [#TableStructure]
SELECT DISTINCT ' + '''' + @DbName + '''' + ' AS DbName
	,SCHEMA_NAME(SCHEMA_ID) AS SchemaName
	,T.NAME AS TableName	
	,C.NAME AS ColumnName
	,CASE 
		WHEN OBJECTPROPERTY(OBJECT_ID(iskcu.CONSTRAINT_NAME), ''IsPrimaryKey'') = 1 
			THEN ''Primary'' 
		WHEN OBJECTPROPERTY(OBJECT_ID(iskcu.CONSTRAINT_NAME), ''IsForeignKey'') = 1 
			THEN ''Foreign''
		ELSE NULL 
		END AS ''KeyType''
FROM SYS.TABLES AS t WITH (NOLOCK)
INNER JOIN SYS.COLUMNS C ON T.OBJECT_ID = C.OBJECT_ID
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS iskcu WITH (NOLOCK) 
ON SCHEMA_NAME(SCHEMA_ID) = iskcu.TABLE_SCHEMA 
	AND T.NAME = iskcu.TABLE_NAME
	AND C.NAME = iskcu.COLUMN_NAME
ORDER BY SchemaName ASC
	,TableName ASC
	,ColumnName ASC;
';

	/*ERROR HANDLING*/
	BEGIN TRY
		EXEC (@sqlCommand)
	END TRY

	BEGIN CATCH
		INSERT INTO #ErrorTable
		SELECT (@sqlCommand)
	END CATCH

	SET @i = @i + 1
END

/*
JOIN THE TEMP TABLES TOGETHER TO CREATE A MAPPED STRUCTURE OF THE SERVER
ADDITIONAL FIELDS ARE ADDED TO MAKE SELECTING TABLES AND FIELDS EASIER
*/
SELECT DISTINCT @@SERVERNAME AS ServerName
	,DL.DbName
	,TS.SchemaName
	,TS.TableName
	,TS.ColumnName
	,TS.[KeyType]
	,',' + QUOTENAME(TS.ColumnName) AS BracketedColumn
	,',' + QUOTENAME(TS.TableName) + '.' + QUOTENAME(TS.ColumnName) AS BracketedTableAndColumn
	,'SELECT * FROM ' + QUOTENAME(DL.DbName) + '.' + QUOTENAME(TS.SchemaName) + '.' + QUOTENAME(TS.TableName) + '--WHERE --GROUP BY --HAVING --ORDER BY' AS [SelectTable]
	,'SELECT ' + QUOTENAME(TS.TableName) + '.' + QUOTENAME(TS.ColumnName) + ' FROM ' + QUOTENAME(DL.DbName) + '.' + QUOTENAME(TS.SchemaName) + '.' + QUOTENAME(TS.TableName) + '--WHERE --GROUP BY --HAVING --ORDER BY' AS [SelectColumn]
	,'SELECT COUNT(*) FROM ' + QUOTENAME(DL.DbName) + '.' + QUOTENAME(TS.SchemaName) + '.' + QUOTENAME(TS.TableName) + 'WITH (NOLOCK)' AS [PerformTableCount]
INTO #MappedServer
FROM [#DatabaseList] AS DL
INNER JOIN [#TableStructure] AS TS ON DL.DbName = TS.DbName
ORDER BY DL.DbName ASC
	,TS.SchemaName ASC
	,TS.TableName ASC
	,TS.ColumnName ASC

/*
HOUSE KEEPING
*/
IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL
	DROP TABLE #DatabaseList;

IF OBJECT_ID('tempdb..#TableStructure') IS NOT NULL
	DROP TABLE #TableStructure;
	
INSERT INTO #TableCount (
	[ServerName]
	,[DatabaseName]
	,[SchemaName]
	,[TableName]
	,[SQLCommand]
	)
SELECT DISTINCT [ServerName]
	,[DbName]
	,[SchemaName]
	,[TableName]
	,[PerformTableCount]
FROM #MappedServer
ORDER BY [ServerName] ASC
	,[DbName] ASC
	,[SchemaName] ASC
	,[TableName] ASC

SET @i = 1
SET @z = (
		SELECT COUNT(*) + 1
		FROM #TableCount
		)
	
WHILE @i < @z
BEGIN
	SET @SQLCommand = (
			SELECT SQLCommand
			FROM #TableCount
			WHERE Id_TableCount = @i
			)

	--ERROR HANDLING
	BEGIN TRY
		INSERT INTO #Count
		EXEC (@SqlCommand)
	END TRY

	BEGIN CATCH
		INSERT INTO #ErrorTable
		SELECT (@sqlCommand)
	END CATCH

	SET @Count = (
			SELECT ReturnedCount
			FROM #Count
			)

	TRUNCATE TABLE #Count

	UPDATE #TableCount
	SET TableCount = @Count
	WHERE Id_TableCount = @i;

	SET @i = @i + 1
END

SELECT *
FROM #ErrorTable;

IF OBJECT_ID('tempdb..#ErrorTable') IS NOT NULL
	DROP TABLE #ErrorTable;

/*
THE DATA RETURNED CAN NOW BE EXPORTED TO EXCEL
USING A FILTERED SEARCH WILL NOW MAKE FINDING FIELDS A VERY EASY PROCESS
*/
SELECT DISTINCT ms.ServerName
	,ms.DbName
	,ms.SchemaName
	,ms.TableName
	,ts.TableCount AS CountOfRows
	,ms.ColumnName
	,ms.KeyType
	,ms.BracketedColumn
	,ms.BracketedTableAndColumn
	,ms.SelectColumn
	,ms.SelectTable
FROM #MappedServer AS ms
LEFT JOIN #TableCount AS ts ON ms.ServerName = ts.ServerName
AND ms.DbName = ts.DatabaseName
AND ms.SchemaName = ts.SchemaName
AND ms.TableName = ts.TableName
ORDER BY ms.DbName ASC
	,ms.SchemaName ASC
	,ms.TableName ASC
	,ms.ColumnName ASC;
	
IF OBJECT_ID('tempdb..#MappedServer') IS NOT NULL
	DROP TABLE #MappedServer;

IF OBJECT_ID('tempdb..#TableCount') IS NOT NULL
	DROP TABLE #TableCount;
	
IF OBJECT_ID('tempdb..#Count') IS NOT NULL
	DROP TABLE #Count;

 

How to get the default error log path for SQL Server with T-SQL

Below is a script to get the default error log path for SQL Server and set it as a variable. 

USE MASTER;
GO

DECLARE @LogPath AS VARCHAR(MAX)
DECLARE @ErrorLogPath TABLE (
	LogDate DATETIME
	,ProcessInfo VARCHAR(255)
	,PathText VARCHAR(MAX)
	);

INSERT INTO @ErrorLogPath
EXEC xp_readerrorlog 0
	,1
	,N'Logging SQL Server messages in file';

SET @LogPath = (
		SELECT REPLACE(REPLACE(REPLACE(PathText, 'Logging SQL Server messages in file ', ''), '''', ''), 'ERRORLOG.', '')
		FROM @ErrorLogPath
		);

SELECT @LogPath AS DefaultLogPath;
GO