Building The Perfect SQL Script. Part 1 of N
Introduction
Im going to start off by saying I am by no means an expert in SQL. I am however on the road to increase my skills and work towards more efficient and cleaner SQL code. With that said, Im going to start an on going series of posts which I will post from time to time which include tips and tricks to help build a better SQL script. Again I am no expert in this subject, so I encourage any who see inefficient code or something done not quite right, please leave a comment and I will fix as soon as I can.
If Exists
Many of us when first learning SQL will have scripts that will create an object (ex stored procedure or view), then afterwards have to change the CREATE keyword to ALTER. What if you didnt want to even worry about this? Use IF EXISTS and you wont have to. You simply query the system, see if the object you are trying to create exists, then drop or create accordingly. The code for this :
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE Name = 'NameOfObject' and type = 'Type') DROP VIEW NameOfObject GO
NameOfObject – The name of the object.
Type – The type of object you are looking for. These include
- C : CHECK constraint
- D : Default or DEFAULT constraint
- F : FOREIGN KEY constraint
- L : Log
- P : Stored procedure
- PK : PRIMARY KEY constraint (type is K)
- RF : Replication filter stored procedure
- S : System tables
- TR : Triggers
- U : User table
- UQ : UNIQUE constraint (type is K)
- V : Views
- X : Extended stored procedure
- TF : Functions
