Tag Archives: tutorial

How to rename and/or remove tables in SQL Server with T-Sql generated by Excel formulas

This post deals with using an Excel file to generate T-Sql code to rename and/or remove tables given a scenario like the following. (To generate T-Sql to remove tables using T-sql see this post.)

Say someone sends you a list via an email or text file of tables they want renamed or removed from a database . You could go into SSMS object explorer and rename or delete each table in the list one by one. Or you could write the T-Sql statements individually but chances are you can speed things up using Excel.

With Excel you can input the schema and table name into a given cell and the T-Sql code will be generated to rename and drop the table using formulas.

To do this you can just download the Excel file template here. Download

Rename And Drop Script Generator

The template is setup assuming you are intending on the dropping the table sometime in the future but first you will be renaming it.

A good approach for removing objects is to rename the objects first. This makes it easier to put the environment back the way it was if there are any problems encountered. After a set period of time if there is no negative impact on the overall environment you can script out the object then drop it. (Obviously do this in a test environment first if possible)

To aid further in a cleanup project the Excel file also acts as a form that can be used to track progress as it contains the columns RenameDate, RestoreDate and DropDate. It also contains the column RestoreOriginalName. This column holds the formula to create the T-Sql code to renamed the tables back if there are any problems encountered.

You can adjust the formula in cell D2 to somethings other than _DELETE_ if you want to change the prefix so the tables will be renamed something else. If you just want to remove the tables you’ll have to run the script from column D before you can drop the tables using the script from column F.

Remember to drag the formula down for as many table entries as you have and it will generate the T-Sql needed.

You can create the Excel file manually yourself without downloading it.

To do so open a new Excel file and in an empty sheet name the first 9 columns as below:

A1: DatabaseName
B1: SchemaName
C1: TableName
D1: RenameForDeletion
E1: RestoreOriginalName
F1: DropTable
G1: RenameDate
H1: RestoreDate
I1: DropDate

For D2 enter the following:

=”USE [“&A2&”]; IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘”&B2&”‘ AND TABLE_NAME = ‘”&C2&”‘)) BEGIN exec sp_rename ‘”&B2&”.”&C2&”‘, ‘_DELETE_”&C2&”‘ END ELSE BEGIN SELECT ‘TABLE [“&A2&”].[“&B2&”].[“&C2&”] DOES NOT EXIST’ AS [RenameFailed] END;”

For E2 enter the following:

=”USE [“&A2&”]; IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘”&B2&”‘ AND TABLE_NAME = ‘_DELETE_”&C2&”‘)) BEGIN exec sp_rename ‘”&B2&”._DELETE_”&C2&”‘, ‘”&C2&”‘ END ELSE BEGIN SELECT ‘TABLE [“&A2&”].[“&B2&”].[“&C2&”] DOES NOT EXIST’ AS [RenameFailed] END;”

For F2 enter the following:

=”USE [“&A2&”]; IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘”&B2&”‘ AND TABLE_NAME = ‘_DELETE_”&C2&”‘)) BEGIN DROP TABLE [“&B2&”].[_DELETE_”&C2&”] END;”

To test that the scripts generated work you can create the mock database and table using the script below. The Excel file is loaded with these values by default.

CREATE DATABASE [TidBytez];
GO

USE [TidBytez]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customer] ([ID] [int] NULL) ON [PRIMARY]
GO

 

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]')


/*
The code below will create a list of all the logins that are members 
of a group.
*/

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 determine where a new database should be deployed

Below I’ve listed 22 questions to consider when deciding where a new SQL Server database should be deployed given the four main options of:

  1. Add Database to existing Instance
  2. Create separate Instance for Database
  3. Create separate VM for Instance and Database
  4. New physical machine needed

There’s no hard and fast rule for determining where a database should go however there are often a lot of limitations dictating your choices. I’ve started the list with a big limitation, money.

If you want to use the list like a score card I’ve prepared one called “New Database Hosting Environment Assessment Form”, I know not the snappiest of titles.

New Database Hosting Environment Assessment Form

Use: I’d recommend stepping through the questions with the requester while gathering feedback. You can then fill in the option scoring with weighted figures or simply use the boxes to record notes. The main deliverable of the form is you will have some documentation that demonstrates the location for the database was assessed and the logic and reasoning for the location chosen has been recorded.

Of course consider adding additional questions relevant to your case to the form. If you think your additional questions would be relevant to everyone deploying a new database free to forward your questions on to me and I’ll include them in the form.

The form can be downloaded here link.

Question 1: Can the business afford a new physical machine?

Considerations: If the financial resources are not available option four
can be eliminated.

Question 2: Are there OS or SQL Server Licensing restrictions?

Considerations: If there are no restrictions to a new VM with it’s own instance this will likely offer the most scalable solution and produce the best performance. If restrictions exist options are limited to 1 & 2.

Question 3: Is the Database the back end for a stand-alone application?

Considerations: The back end for a stand-alone application should be isolated as much as possible from other impacting factors. The higher the number of the option chosen the better.

Question 4: What is the primary purpose of the Database?

Considerations: What is the business use\s and in what capacity will the database function i.e. Transactional Db, Reporting Db, Datastore?

Question 5: Do you want\need to be able to limit access to hardware resources?

Considerations: Access to resources, ram etc. can only be limited at an instance level. Option 1 cannot be used.

Question 6: Are there any SQL Server Service Pack dependencies?

Considerations: SQL Server can have different SPs on different instances but cannot have different SPs for different databases within an instance.

Question 7: What is the current excess capacity of the hardware?

Considerations: Can the Hardware handle the additional workload? If not either reduce resource consumption of resources on the hardware, add RAM and/or cores or choose option 4.

Question 8: Is there a VM capacity restraint on the hardware?

Considerations: Can the hardware actually host a new VM without a trade off in resources or a decrease in performance? If restrained option 3 cannot be used.

Question 9: What is the VM capacity?

Considerations: Is the OS already maxed out on resources? Can the VM handle the additional workload?

Question 10: Is there an expected increase in size and usage in the Database over time?

Considerations: If known and minimal option 1 can be used. If unknown or unlimited the higher the number of the option chosen the better.

Question 11: Is the resource usage of the new Database known?

Considerations: Benchmarking RAM, CPU and bandwidth usage should be carried out prior to installation.

Question 12: What are the disaster recovery requirements?

Considerations: Should the databases that share common dependencies be hosted on
the same server?

Question 13: What is the required operational up time? 24/7 etc.

Considerations: Does this operational run time tie in with the rest of the databases
on the instance, or the instances hosted on a VM or physical server?

Question 14: What are the Maintenance requirements?

Considerations: Will new index rebuilds etc. take a long time and effect the schedule of the instance overall?

Question 15: What are the Backup requirements?

Considerations: Will the backup schedule of the other databases be impacted
by the addition?

Question 16: Is the Database functionally similar or supporting databases currently on the instance?

Considerations: Does the new Database logically fit in with the database/s currently running on an instance?

Question 17: Have server security concerns been addressed?

Considerations: Will people with administrative access to the server have access to a Database and data they should not be able to see?

Question 18: Does hosting particular databases together compound the potential
damage If an unauthorised person was to gain access to the server?

Considerations: Will an unauthorised person have access to data that represents a major security breach if the data is combined from different databases?

Question 19: Does a vendor have control over the instance or server?

Considerations: Will putting a Database on a particular instance leave you at the mercy of a vendor?

Question 20: Will stacking instances make tracking down performance issues across
the VM difficult?

Considerations: Will this create a server level noisy neighbour problem?

Question 21: Will packing databases too densely make tracking down performance issues across the Instance difficult?

Considerations: Will this create an instance level noisy neighbour problem?

Question 22: Will moving the Database to be hosted somewhere else in the
future be possible?

Considerations: Does this decision need to be gotten right the first time?

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

 

How to pass arguments from command line to a console application written in C#

This is a simple tutorial on passing arguments or parameter values from command line to a console application written in C#. Using the example below you should be able to edit and expand on the logic to fit your own needs.

First you’ll need to create a new Visual Studio C# console application, to do so follow these steps:

To create and run a console application

  1. Start Visual Studio.

  2. On the menu bar, choose FileNewProject.
  3. Expand Installed, expand Templates, expand Visual C#, and then choose Console Application.
  4. In the Name box, specify a name for your project, and then choose the OK button.
  5. If Program.cs isn’t open in the Code Editor, open the shortcut menu for Program.cs in Solution Explorer, and then choose View Code.
  6. Replace the contents of Program.cs with the following code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TestArgsInput
{
    class Program
    {
        static void Main(string[] args)
        {
            if (args.Length == 0)
            {
				// Display message to user to provide parameters.
                System.Console.WriteLine("Please enter parameter values.");
                Console.Read();
            }
            else
            {
                // Loop through array to list args parameters.
                for (int i = 0; i < args.Length; i++)
                {
                    Console.Write(args[i] + Environment.NewLine);
                    
                }
                // Keep the console window open after the program has run.
                Console.Read();
            }
        }
    }
}

 

The Main method is the entry point of a C# application. When the application is started, the Main method is the first method that is invoked.

The parameter of the Main method is a String array that represents the command-line arguments. Usually you determine whether arguments exist by testing the Length property as in the example above.

When run the example above will list out the parameters you have provided to the command window. The delimiter for command line separating arguments or parameter values is a single space. For example the following would be interpreted as two arguments or parameter values:

“This is parameter 1” “This is parameter 2”

If the arguments were not enclosed by double quotes each word would be considered an argument.

To pass arguments to the console application when testing the application logic the arguments can be written into the debug section of the project properties as shown below.

TestArgs

So using the if the app is run with the command line arguments provided as above in the image the command window will list:
Parameter 1
Parameter 2
If you would like to know how to create a console application in Visual Studio that won’t open a command window when it runs see this tutorial link.
If you would like to know how to create a csv file with C# see this tutorial link.

How to copy a large result set from SQL Server Management Studio to Excel

So you’ve tried copying and pasting the results of a query into an excel file only to get the out of memory exception. Now the fun starts!

There are multiple options to achieve copying data from SQL Server Management Studio to excel however most of them are a pain.

You’ve tried the save as option but the csv and text files have jumbled up content. You could use the Management Studio export function, but this is intended for physical tables not results sets and you might not have the permissions to create tables in the environment. You could use the bcp Utility but you’ve probably read leaving this option turned on represents a security risk. You could create an SSIS package . . . yeah that’s an efficient and effective option when you just want the damn results of an ad hoc query!!!

I’d suggest splitting the result set into chunks. You can then copy and paste the chunks into the excel file without running out of memory. Sure it’s kinda manual but trust me it’ll take less time than the options above.

I’d wager you probably only need the result set split into two, so you’ve to copy and paste twice rather than once. Not that big a deal right? I’ve even provided some code below that will really move things along.

Start by writing your query results into a temporary tablet called #QueryResult, for example SELECT * INTO #QueryResult FROM TableName.

Then all you need to do is determine how many segments you need. NTILE(n) is a function that allocates your output into n segments, each of the same size (give or take rounding when the number of rows isn’t divisible by n).

So this produces an output like:

Id Name Ntile
1 Mickey 1
2 Leo 1
3 Raph 2
4 Donnie 2

Start by leaving n set to the default of 2. Once the data is written to the table #QueryResult run the code below in the same SSMS window the temp table was created in. Running the code should produce the same number of returned result sets as the n value you provided. Use a higher n number to create more segments if you still run out of memory when you try to copy and paste the first segment.

/*
Write your query results to a temp table here
i.e. SELECT * INTO #QueryResult FROM TableName
*/
DECLARE @n INT
DECLARE @i INT

/*
Set n to how many segments/results set returned you need
*/
SET @n = 2
SET @i = 1

SELECT *
	,NTILE(@n) OVER (
		ORDER BY RowNum
		) AS NtileGroup
INTO #Export
FROM (
	SELECT ROW_NUMBER() OVER (
			ORDER BY (
					SELECT NULL
					)
			) AS RowNum
		,*
	FROM #QueryResult
	) AS a

WHILE @i <= @n
BEGIN
	SELECT *
	FROM #Export
	WHERE NtileGroup = @i
	ORDER BY RowNum ASC

	SET @i = @i + 1
END

DROP TABLE #QueryResult

DROP TABLE #Export

 

So that’s it, you should now be able to copy and paste your results. Maybe someday in the future Microsoft will add the option of saving results directly to excel . . .