Tag: T-SQL

Check for Objects in SQL Server

Here are some examples of how to check for existing objects in SQL Server.  Use them before you drop/create/modify to avoid errors.

Object Type Existence Checks:

Column Check

Using INFORMATION_SCHEMA.COLUMNS

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
	WHERE
	TABLE_SCHEMA = 'dbo'
	AND TABLE_NAME = 'MyTable'
	AND COLUMN_NAME = 'MyColumn')
BEGIN
	PRINT 'Altering MyTable to include the MyColumn';
	ALTER TABLE dbo.MyTable ADD MyColumn INT NULL;
END
GO

Using COL_LENGTH

IF COL_LENGTH('MyTable', 'MyColumnName') IS NOT NULL
BEGIN
	PRINT 'Dropping MyColumnName column on MyTable.';
	ALTER TABLE MyTable DROP COLUMN MyColumnName;
END
GO

Column Data Type Check

Check if a column has a datatype of varchar(200), if not, change it

IF NOT EXISTS( SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'VARCHAR'
AND CHARACTER_MAXIMUM_LENGTH = 200
AND TABLE_NAME = 'MyTable'
AND COLUMN_NAME = 'MyColumn')
BEGIN
	PRINT 'Changing datatype of MyTable.MyColumn to vachar(200)';
	ALTER TABLE MyTable
	ALTER COLUMN MyColumn NVARCHAR(200) NULL;
END

Column Default Value Check

IF NOT EXISTS(
	SELECT *
		FROM sys.all_columns c
		JOIN sys.tables t
			ON t.object_id = c.object_id
		JOIN sys.schemas s
			ON s.schema_id = t.schema_id
		JOIN sys.default_constraints d
			ON c.default_object_id = d.object_id
	WHERE
		s.name = 'MySchema'
		AND t.name = 'MyTable'
		AND c.name = 'MyColumn')
BEGIN
	ALTER TABLE [MyTable]
	ADD CONSTRAINT [MyConstraint]
	DEFAULT ((0)) FOR [MyColumn];
END
GO

Foreign Key Check

IF OBJECT_ID('FK_MyForeignKey', 'F') IS NOT NULL
BEGIN
	PRINT 'Removing old foreign key reference';
	ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [FK_MyForeignKey];
END
GO

Function (Scalar) Check

IF OBJECT_ID ('MyFunction'. 'FN') IS NOT NULL
BEGIN
	PRINT 'Dropping MyFunction';
	DROP FUNCTION MyFunction;
END
GO

Index Check

IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'idx_MyIndex')
BEGIN
	PRINT 'Dropping idx_MyIndex to recreate it.'
	DROP INDEX idx_MyIndex ON dbo.MyTable;
END
GO
PRINT 'Creating idx_MyIndex';
GO
CREATE NONCLUSTERED INDEX idx_MyIndex
	ON [dbo].[MyTable] ([myRelatedCol1],[myRelatedCol2],[myRelatedCol3])
	INCLUDE ([myRelatedCol4])
GO

Linked Server Check

IF NOT EXISTS(
	SELECT * FROM sys.servers
	WHERE name = N'MyLinkedServerAliasName')
BEGIN
	--do something
END

Primary Key Check

--check for PK on a schema'd table
IF EXISTS (
	SELECT * FROM sys.key_constraints
	WHERE type = 'PK'
	AND OBJECT_NAME(parent_OBJECT_ID) = 'MyTable'
	AND SCHEMA_NAME([schema_id]) = 'MySchema'
)
BEGIN
	PRINT 'Dropping primary key from MySchema.MyTable';
	ALTER TABLE [MySchema].[MyTable] DROP CONSTRAINT PK_MyPrimaryKey
END
GO

Schema Check

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'MySchema')
BEGIN
	PRINT 'Creating schema MySchema';
	EXEC('CREATE SCHEMA [MySchema] AUTHORIZATION [dbo]');
END

Stored Procedure Check

IF OBJECT_ID(N'MyProcedure', 'P') IS NOT NULL
BEGIN
	PRINT 'Dropping MyProcedure';
	DROP PROCEDURE MyProcedure;
END
GO

Table Check

IF OBJECT_ID(N'dbo.MyTable', 'U') IS NOT NULL
BEGIN
	PRINT N'Dropping dbo.MyTable';
	DROP TABLE dbo.MyTable;
END
GO

Login (Server Level) Check

IF NOT EXISTS (
	SELECT *
	FROM master.sys.server_principals
	WHERE name = 'MyServerLoginName') 
BEGIN
	PRINT 'Creating server login: MyServerLoginName';
	CREATE LOGIN [MyServerLoginName] 
	WITH PASSWORD = N'Password', 
	DEFAULT_DATABASE = [MyDatabase], 
	CHECK_EXPIRATION = OFF, 
	CHECK_POLICY = OFF
END
GO

User (Database Level) Check

IF NOT EXISTS(
	SELECT *
	FROM sys.database_principals
	WHERE name = 'MyDbUsername')
BEGIN
	--do someting
        PRINT 'Creating database user';
	CREATE USER [MyDbUsername] FOR LOGIN [MyServerLoginName]
END

You can also link the database user up with the server login like so:

USE MyDatabase;

SELECT
	sp.name AS ServerLoginName,
	dp.name AS DBUserName
FROM
	sys.server_principals sp
	LEFT JOIN
	sys.database_principals dp ON sp.sid = dp.sid
WHERE
	sp.name = 'MyLogin';

View Check

IF OBJECT_ID(N'MyView', 'V') IS NOT NULL
BEGIN
	PRINT 'Dropping MyView. Its no longer needed.';
	DROP VIEW [dbo].[MyView];
END
GO

Current Executing Database

IF (db_name() = 'MyDatabase')
BEGIN
	PRINT 'We are executing this call against MyDatabase';
END
GO

Notifying DBAs of New SharePoint Databases

In our production environment, the Database Administrators like to know when a new database is created or dropped. As well they should.  They normally use a DDL trigger to send out an email when one of these events occurs.  The problem we ran into was that in Windows SharePoint Services 3 (WSS3/SharePoint 2007), the trigger broke SharePoint’s ability to create new content databases!  Perhaps the account that ran the procedure didn’t have enough rights to run sp_send_dbmail, but we don’t know for sure.  If you know why, tell everyone in the comments.

The way we fixed it was to, instead, create a nightly agent job on the SQL Server that just looked for new database records in sys.databases.

SELECT name, create_date
FROM sys.databases
order by create_date desc

Just add a WHERE clause that filters the databases based on how often you run your job. If any databases exist since the last time you ran the job, send out an email.

Quickly See a Table’s Columns in SSMS

When looking at long queries you’ll typically need to remember what data is in one of the tables.  SSMS allows the creation of shortcut keys that can be used to quickly look at the first row in a table.  You’ll then be able to see the column names and some example data.

In SQL Server Management Studio (2012 shown), create a new shortcut key by going to:

Tools > Options > Environment > Keyboard > Query Shortcuts

Add the beginning of a query to the shortcut key you like, later on you’ll see how the table name will be appended when the query is run.

SSMS Query Shortcuts

“SELECT TOP 1 * FROM”

After you click ok on the dialog, and create a new query window, you’ll be able to select a table name in a query and use your shortcut key.  The selection in the query analyzer will be appended to your shortcut query and run.

Select the full table name and press your new shortcut key.
Select the full table name and press your new shortcut key.

The screenshots are from SQL Server Management Studio 2012, but older versions have the shortcut key option too.  I bet it even works in the old Query Analyzer.

Cool eh?  Maybe you like to get the top 200 rows to get a better picture of the table’s data?  I’m sure you’ll have tons of ideas on cool shortcuts you can run.  Be nice and leave me some ideas in the comments.