If Exists command in SQL Server
“If Exists” accepts the select statement or any string/number & returns true or false.Sometimes we come across a condition where if some value exists in table we want to perform certain activity. That time we can use “If Exists” clause.
Declare @word12 int;
IF EXISTS (SELECT itemId FROM ItemTable WHERE itemNum=’181005′)
set @word12 = 1
set @word12 = 0
Above query checks for ItemNum in Table ItemTable & selects ItemId. If ItemId Exists then @word12 will be set as 1 else as 0.
Searching Table/Column in database
Searching Column in database
select * from INFORMATION_SCHEMA.columns where column_name = ‘custom’
Searching Tables in Databases
select * from INFORMATION_SCHEMA.Tables where table_name like ‘%backup%’
The above query search for the column/table in the complete Sql Server instance for the searched column/table & returns the column/table information.
Guidelines to Create/Alter Store Procedures
1. Create Store Procedure & name it based on date of creation. Prefix store procedures names with number to maintain the order of execution if Store Procedures (sp).
1 represents the execution order of Store Procedures (sp).
“CreateStoreProcedure” is the name of Store Procedures (sp).
“Create” indicates the type of Store Procedures (sp).
MMDDYY is the date format.
Note: for altering Store Procedures (sp) same guidelines can be followed by replacing create with alter.
2. On creation of Store Procedures (sp) add commenting to it.
Comments should be added to get the information about the Store Procedures (Sp) creation data, modified date & author information.
E.g. At the start of sp
Author: Author Name
Create date: 28 July 2014
Description: Purpose of SP
3. Script should contain go keyword at the end of sp.
4. Try to add description in design of table against the column if column is having different name than its associated column.
E.g. in third party softwares like ERP, Excel files column name is “Csutom#” but in table we write “CustomNumber”. To show mapping betwwen those 2 columns just add the description against the column to make it more readable & understandable.
5. Table column should not contain Special characters or numbers or spaces (underscore (_) can be used).