Tag: 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

How to Run SQL Server Management Studio as a Different Windows User

If you’re using Windows Authentication for your SQL Server database you’ll need to run SQL Server Management Studio (SSMS) as that Windows account to test queries.  SSMS normally runs as the user logged in to Windows, but there is a simple way to run it as the database access account.

Just shift-right-click on a shortcut and select “Run as a different user“.  (Windows 7)

Shift-Right-Click a shortcut to run the program as a different Windows User.
Shift-Right-Click a shortcut to run the program as a different Windows User. (Windows 7)

You’ll be presented with a Windows login prompt.  Log in and SSMS will open.  Note that the connection dialog has the new user in the Windows Authentication area

Connect to Server Dialog
Connect to Server Dialog

Note: You’re now running the program as a different user, so it probably won’t have access to the files you normally use.  My fix was to give the account read/write to the folder where I hold my SQL scripts.

This tip will work for other programs too.  Let me know how you used it in the comments below.

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.

New Version of SQL Server To Launch in March 2012

They really feel like they just keep on coming. It feels like only yesterday that we finally upgraded to SQL Server 2008 R2 here. In fact, I think it was 6 months ago, in 2011. Not like I need to tell you, but it’s hard to keep up.

But here it is. SQL Server 2012. If you’re interested in learning more about what’s in the upcoming version, you can register for the virtual launch event being held on 3/7/2012 at sqlserverlaunch.com.

Now, to tell the truth, I really miss the days of local launch events, with all the swag and actual people. Maybe Microsoft will bring one to Milwaukee in the near future. Hopefully to launch the new Visual Studio. And, I’m really hoping for a new lunch bag, cause mine from the Visual Studio 2010 launch event is getting pretty ragged.

I use this everyday. Well, every day I'm not lazy and actually bring lunch to work.

Back to SQL Server 2012..

At this point, I’m not seeing anything that’s really jumping out at me as a must-need feature.  It’s not a surprise that Microsoft is putting more behind the cloud computing fad, but this doesn’t really interest me.  I write intranet applications and we don’t really have a need to go all cloudy.  The other features don’t seem to cool to me either.  But time will tell.  Maybe one of you will let me know what I should be paying attention to in the comments.

For now, I’m sticking with 2008 R2.

You can read more about SQL Server 2012 RC0 here.