SQL Server

Check if a database exists on a server

Here is some code to use to check if a database exists. Just replace the string 'DATABASE NAME’. SELECT * FROM [master].[sys].[databases] WHERE name='DATABASE NAME'

How to switch Entity Framework database connected to

If you have a connection to a database through entity framework and you need to switch it to another database (with the exact same structure) you just need to set the Connection.ConnectionString (as seen below). I had an application where we created a copy of the Master database when setting up a new client.  So using Entity Framework I switched from the master database to the client (depending on what the admin was doing). using (MasterEntities aEntities = new MasterEntities()) { //Switch db connected to aEntities.Database.Connection.ConnectionString = aEntities.Database.Connection.ConnectionString.Replace("OldDatabaseName", "NewDatabaseName"); ...

List all indexes in a database

This script will list all indexes and their types (clustered/nonclustered). SELECT so.name AS TableName , si.name AS IndexName , si.type_desc AS IndexType FROM sys.indexes si JOIN sys.objects so ON si.[object_id] = so.[object_id] WHERE so.type = 'U' --Only get indexes for User Created Tables AND si.name IS NOT NULL ORDER BY so.name, si.type

Summary of last night’s job run

Here is a script that will provide a summary of jobs that ran last night. This is helpful if you want a summary of your jobs instead of an email from each job. SELECT j.name, last_run_outcome = CASE WHEN js.last_run_outcome = 0 THEN 'Failed' WHEN js.last_run_outcome = 1 THEN 'Succeeded' WHEN js.last_run_outcome = 2 THEN 'Retry' ...

How to find out what triggers you have.

I try to avoid using triggers because I always forget they are there.  I have been burned more than once by triggers updating data when I didn’t know they were there.  I have also been troubleshooting someone else’s database and been bitten by triggers. The code from this post I copied from Chris McGowan’s blog.  He wrote up a great article about trigger status.  His post has code to create a job to email you.  I modified it to just run in SSMS and use a table variable (in case you don’t have the permissions to create...

Query to database information

Prashanth Jayaram provided a blog post about a script he has to get information about databases on your server. This is a perfect script for keeping up with what’s going on in your environment or getting an idea about what’s going on a client’s site. The script Prashanth provided had some errors, so I corrected those you can just copy the code below, paste it in SSMS and run it. This is the information returned: Servername Database Name ...

What little I know about SQL Bulk Insert

I’ve got a stored procedure to upload a csv file to a database table.  From what I’ve read that’s the fastest way to load csv data to SQL. Cool, but I’ve had errors when I went to deploy to production. Here are just a few things I’ve learned. 1. The permissions used are different when using windows login vs sql login (to call the stored procedure). 2. If you use a windows login to call the stored...