Will "the Mighty" Strohl

A Few Quick and Common SQL Server Programming Tips

There are great many things that we realize we take for granted when hand-coding database updates.  For instance, in the SQL Server Management Studio (or Enterprise Manager), we often right-click something and then let a wizard walk us through some steps and... POOF!  Your database is updated!

For those of you don't know, what that wizard is really doing is generating SQL statements for you to perform the update.  Generally, those SQL statements are much more complicated than any you or I would usually write.  So it is great that there is a GUI that takes care of this for us.  But what about when there isn't a GUI to take care of certain things?

TABLE INFORMATION

Here is a snippet that will afford you a great deal of help when writing T-SQL to manage your Database.

EXEC sp_help [TABLENAME]

What this does is offer you a whole lot of information about the specified table.  Some of this, you may not even have known prior to executing the code above.  You will be offered information about your table columns, constraints, keys, and more.  Basically, most of the information offered to you when in design view for the table.

Does a Table Even Exist?

Sometimes, we need to know if a table actually exists prior to executing commands on the table.  For instance, we cannot issue alter command on a table if the table was never created.  For this reason, the following little snippet is HIGHLY useful.

IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'YourTableName')
BEGIN
-- your logic here
END

Does a Foreign Key Exist?

What about the instances of when we want to know if there is a foreign key constraint named "FK_table1_table2"?  The following snippet would help you.  However, you would obviously need to first know the name of the object you are looking for.  Another piece of information that would be extremely useful, is the type of object you are looking for.

IF EXISTS(SELECT 1 FROM [dbo].[sysobjects] WHERE [name] = 'FK_table1_table2' AND [type] = 'F')
BEGIN
-- YOUR LOGIC HERE
END

I hope these little tips help you during your adventures with T-SQL...



blog comments powered by Disqus