SQL Script to Generate Insert Query Script from Given Table Name and Condition

This post will help developer create insert script from the table with some condition or in other term to create insert script from given select statement.
This will be helpful when we need to move some limited data from one environment to other.

DECLARE 
 @Table_name  varchar(240), 
 @Condition  Varchar(MAX) 

SET @Table_name='NotificationBase'
SET @Condition = ' NotificationID IN (''11DB04B4-3F10-422F-B6C5-89914E7FD293'', ''4EB8D85E-6817-4220-93DF-54B84B3CFE62'')'
                            
DECLARE @COLUMNS  table (Row_number SmallINT , Column_Name varchar(Max) )                            
DECLARE
 @Schema_name  varchar(30),  
 @Conditions as varchar(MAX), 
 @Total_Rows as SmallINT,
 @Counter as SmallINT,         
    @ComaCol as varchar(max),
 @Query AS VARCHAR(MAX),
 @InsertQuery VARCHAR(MAX),
 @FinalQuery VARCHAR(MAX)

SET @Schema_name = 'dbo'
SET @ComaCol=''           
Set @Counter=1                             
set @Conditions=''  
SET @Query =''
SET @InsertQuery = ''
SET @FinalQuery = ''                           
                            
                            
INSERT INTO @COLUMNS                             
 SELECT  Row_number()Over (Order by column_id ) [Count] , Name 
 FROM SYS.COLUMNS WHERE Object_Name(Object_ID) = @Table_name AND system_type_id <> 189
  AND is_identity = 0 AND is_computed = 0

                            
SELECT @Total_Rows= Count(1) FROM  @COLUMNS                             
                          
Set @Table_name= '['+@Table_name+']' 
Set @Schema_name='['+@Schema_name+']'                     
                            
While (@Counter <= @Total_Rows )                             
BEGIN
 SELECT @ComaCol= @ComaCol + '['+Column_Name+'], '           
    FROM @COLUMNS                             
 WHERE [Row_number] = @Counter                         
                            
 SELECT @Query = @Query + ' + Case When [' + Column_Name + '] is null then ''NULL'' Else '''''''' + 
  Replace( Convert(varchar(Max),[' + Column_Name + ']  ) ,'''''''',''''  ) +'''''''' end+' + ''','''                             
    FROM @COLUMNS 
 WHERE [Row_number]=@Counter                             
      
 SET @Counter=@Counter+1    
End                        
                            
SET @Query = Right(@Query,LEN(@Query)-2)
SET @Query= LEFT(@Query,LEN(@Query)-4)             
SET @ComaCol= substring (@ComaCol,0,  len(@ComaCol) )                           
                            
SET @InsertQuery= '''INSERT INTO ' + @Schema_name + '.' + @Table_name + '(' + @ComaCol + ')' + ' Values( ' + '''' + '+' + @Query  + '+' + ''')'''                            
                            
SET @FinalQuery= 'SELECT  ' + @InsertQuery +'FROM  ' + @Schema_name + '.' + @Table_name + ' With(NOLOCK) ' + ' WHERE ' + @Condition    
                        
PRINT(@FinalQuery)                             
EXECUTE(@FinalQuery)      

Remove Duplicate Records from SQL table

This Code will help to remove duplicate records when there is not key field in the table.

/* Create Table Statement */
CREATE TABLE #Employee
(
Name varchar(55) NULL,
Salary decimal(10, 2) NULL,
Designation varchar(20) NULL
 )

 /* Insert Data into the Table*/
 INSERT INTO #Employee VALUES('Lakhan Pal Garg', 10000, 'AST')
 INSERT INTO #Employee VALUES('Lakhan Pal Garg', 10000, 'AST')
 INSERT INTO #Employee VALUES('Amit Tyagi', 10000, 'ITA')
 INSERT INTO #Employee VALUES('Amit Tyagi', 10000, 'ITA')
 INSERT INTO #Employee VALUES('Lakhan Pal Garg', 12000, 'AST')
 INSERT INTO #Employee VALUES('Amit Tyagi', 13000, 'ITA')
 INSERT INTO #Employee VALUES('Sumit Sharma', 15000, 'AST')
 INSERT INTO #Employee VALUES('Sumit Sharma', 15500, 'AST')
 
/* Mark Duplicate Record */
WITH TempEmp (Name,duplicateRecCount)
AS
(
SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Salary, Designation ORDER BY Name)
AS duplicateRecCount
FROM #Employee
)

/* Now Delete Duplicate Records */
DELETE FROM TempEmp
WHERE duplicateRecCount > 1

/* See updated table data */
Select * from #Employee 

Create Generic Template to Create Store Procedure

This store procedure will be used to create a generic SP for insert, update, delete, select, select all statement.

This will create a store procedure script for the given table and SPType (Insert, Update, Select, SelectAll, Delete).

Copy the following Store Procedure:

/*
Created By: Lakhan Pal
Created On: 07/02/2012
Purpose: To Create Store Procedure Template for Given Table for
Insert, Update, Select, SelectAll, And Delete Script

User Just need to Pass Following Arguments
(1) @TableName VARCHAR(100)
(2) @AuthorName VARCHAR(100)
(3) @SPType VARCHAR(15) (Insert, Update, Select, SelectAll, Delete)
(4) @Comment VARCHAR(500) [Optional]

Execution Statement:
EXEC uspCreateGenericTemplateForSP 'tblUsers', 'Lakhan Pal', 'Insert'
EXEC uspCreateGenericTemplateForSP 'tblUsers', 'Lakhan Pal', 'Update'
EXEC uspCreateGenericTemplateForSP 'tblUsers', 'Lakhan Pal', 'Select'
EXEC uspCreateGenericTemplateForSP 'tblUsers', 'Lakhan Pal', 'SelectAll'
EXEC uspCreateGenericTemplateForSP 'tblUsers', 'Lakhan Pal', 'Delete'
*/
CREATE PROCEDURE uspCreateGenericTemplateForSP
(
@TableName VARCHAR(100),
@AuthorName VARCHAR(100),
@SPType VARCHAR(15),
@Comment VARCHAR(500)=NULL
)
AS
BEGIN
DECLARE @MaxID INT, @ColList VARCHAR(4000), @paramList VARCHAR(4000)
DECLARE @IsIdentityExist Int
DECLARE @ScriptTable Table(ID INT IDENTITY(1,1),Script VARCHAR(5000), RecordType Char(1))

SET @IsIdentityExist =0
SET @ColList =''
SET @paramList =''

IF (@Comment IS NULL)
SET @Comment =' To Create script for ' + @SPType
+ ' Record into Table ' + @TableName

/* To Check the Identity Column */
SELECT @IsIdentityExist =COUNT(Name) FROM sys.columns
WHERE object_id=object_id(@TableName) AND is_identity =1

/* Comment Section - Start Here */
INSERT INTO @ScriptTable(Script) VALUES('/*')
INSERT INTO @ScriptTable(Script)
VALUES('Created By: ' + @AuthorName)
INSERT INTO @ScriptTable(Script)
VALUES('Created On: ' + CONVERT(VARCHAR(10), GETDATE(), 103))
INSERT INTO @ScriptTable(Script)
VALUES('Purpose: ' + @Comment)
INSERT INTO @ScriptTable(Script) VALUES('*/')
/* Comment Section - End Here */

INSERT INTO @ScriptTable(Script)
VALUES('CREATE PROCEDURE usp' +@SPType + '_'+ @TableName )
INSERT INTO @ScriptTable(Script) VALUES('(')

/* If Identity Column Exists */
IF (@IsIdentityExist>0)
BEGIN
INSERT INTO @ScriptTable(Script, RecordType)
(
SELECT ' @' + C.Name + ' ' + T.Name + ',' , 'P'
FROM Sys.COLUMNS C INNER JOIN SYS.TYPES T
ON C.System_Type_ID= T.System_Type_ID
WHERE Object_ID = OBJECT_ID(@TableName) AND C.is_identity =1)
END

IF(@SPType ='Insert' OR @SPType ='Update')
BEGIN
/* Parameter Section - Start Here */
INSERT INTO @ScriptTable(Script, RecordType)
(
SELECT ' @' + C.Name + ' ' + T.Name + ',' , 'P'
FROM Sys.COLUMNS C INNER JOIN SYS.TYPES T
ON C.System_Type_ID= T.System_Type_ID
WHERE Object_ID = OBJECT_ID(@TableName) AND C.is_identity =0 AND
T.System_Type_ID NOT IN (173, 175, 239, 231,165, 167,106, 108 )
UNION
SELECT ' @' + C.Name + ' ' + T.Name + '(' +
CONVERT(VARCHAR(10),C.MAX_Length)
+ ', '+ CONVERT(VARCHAR(10),C.Precision) + ')' + ',', 'P'
FROM Sys.COLUMNS C INNER JOIN SYS.TYPES T
ON C.System_Type_ID= T.System_Type_ID
WHERE Object_ID = OBJECT_ID(@TableName) AND C.is_identity =0
AND T.System_Type_ID IN (106, 108)
UNION
SELECT ' @' + C.Name + ' ' + T.Name + '(' +
CONVERT(VARCHAR(10),C.MAX_Length) + ')' + ',' , 'P'
FROM Sys.COLUMNS C INNER JOIN SYS.TYPES T
ON C.System_Type_ID= T.System_Type_ID
WHERE Object_ID = OBJECT_ID(@TableName) AND C.is_identity =0
AND T.System_Type_ID IN (173, 175, 239, 231,165, 167 )
)
/* Parameter Section - End Here */
END

SELECT @MaxID = MAX(ID) FROM @ScriptTable WHERE RecordType='P'
UPDATE @ScriptTable SET Script =
SUBSTRING(Script,0,Len(Script)) WHERE ID=@MaxID

INSERT INTO @ScriptTable(Script) VALUES(')')
INSERT INTO @ScriptTable(Script) VALUES('AS')
INSERT INTO @ScriptTable(Script) VALUES(' BEGIN')

/* Query Section - Start Here */
IF(@SPType = 'Insert')
BEGIN
SELECT @ColList = @ColList + name +',',
@paramList = @paramList + '@' + name +','
FROM sys.columns
WHERE object_id=object_id(@TableName) AND is_identity =0

SET @ColList = SUBSTRING(@ColList,0,Len(@ColList))
SET @paramList = SUBSTRING(@paramList,0,Len(@paramList))

INSERT INTO @ScriptTable(Script)
VALUES(' INSERT INTO ' + @TableName )
INSERT INTO @ScriptTable(Script)
VALUES(' (' + @ColList + ')')
INSERT INTO @ScriptTable(Script)
VALUES(' VALUES (' + @paramList + ')')
END
ELSE IF (@SPType ='Update')
BEGIN
SELECT @ColList = @ColList + name +' = ' + '@' + name + ','
FROM sys.columns
WHERE object_id=object_id(@TableName) AND is_identity =0

SELECT @paramList = @paramList + name +' = ' + '@' + name + ','
FROM sys.columns
WHERE object_id=object_id(@TableName) AND is_identity =1

SET @ColList = SUBSTRING(@ColList,0,Len(@ColList))
IF (@IsIdentityExist>0)
SET @paramList = 'WHERE ' + SUBSTRING(@paramList,0,Len(@paramList))
ELSE
SET @paramList =''

INSERT INTO @ScriptTable(Script)
VALUES(' UPDATE ' + @TableName)
INSERT INTO @ScriptTable(Script)
VALUES(' SET ' + @ColList)
INSERT INTO @ScriptTable(Script)
VALUES(' ' + @paramList)
END
ELSE IF(@SPType= 'Select')
BEGIN
SELECT @ColList = @ColList + name +','
FROM sys.columns
WHERE object_id=object_id(@TableName)AND is_identity =0

SELECT @paramList = @paramList + name +' = ' + '@' + name + ','
FROM sys.columns
WHERE object_id=object_id(@TableName) AND is_identity =1

SET @ColList = SUBSTRING(@ColList,0,Len(@ColList))
IF (@IsIdentityExist>0)
SET @paramList = 'WHERE ' + SUBSTRING(@paramList,0,Len(@paramList))
ELSE
SET @paramList =''

INSERT INTO @ScriptTable(Script)
VALUES(' SELECT ' + @ColList)
INSERT INTO @ScriptTable(Script)
VALUES(' FROM ' + @TableName)
INSERT INTO @ScriptTable(Script)
VALUES(' ' + @paramList)
END
ELSE IF(@SPType= 'SelectAll')
BEGIN
SELECT @ColList = @ColList + name +','
FROM sys.columns
WHERE object_id=object_id(@TableName)

SET @ColList = SUBSTRING(@ColList,0,Len(@ColList))

INSERT INTO @ScriptTable(Script)
VALUES(' SELECT ' + @ColList)
INSERT INTO @ScriptTable(Script)
VALUES(' FROM ' + @TableName)
END
ELSE IF(@SPType ='Delete')
BEGIN
SELECT @paramList = @paramList + name +' = ' + '@' + name + ','
FROM sys.columns
WHERE object_id=object_id(@TableName) AND is_identity =1

IF (@IsIdentityExist>0)
SET @paramList = 'WHERE ' + SUBSTRING(@paramList,0,Len(@paramList))
ELSE
SET @paramList =''

INSERT INTO @ScriptTable(Script)
VALUES(' DELETE FROM ' + @TableName)
INSERT INTO @ScriptTable(Script)
VALUES(' ' + @paramList)
END
/* Query Section - End Here */

INSERT INTO @ScriptTable(Script) VALUES(' END')

SELECT Script FROM @ScriptTable
END

Create Auto Implemented Public Properties To Be Used in C# Code From SQL Server

This script will help you to create Auto Implemented public properties.
For this you just need to pass the table name and the Script will create properties for each column available in that Table.

Copy the following code to generate the properties:

DECLARE @TableName VARCHAR(100)
SET @TableName = 'tblUsers'
--String
SELECT 'public string ' + name + ' { get; set; }'
FROM Sys.COLUMNS WHERE Object_ID = OBJECT_ID(@TableName)
AND System_Type_ID IN (35, 36, 98, 99,165,167,173,175,231,239,241,231,189)
UNION
-- Int64
SELECT 'public Int64 ' + name + ' { get; set; }'
FROM Sys.COLUMNS WHERE Object_ID = OBJECT_ID(@TableName)
AND System_Type_ID IN (127)
UNION
-- Int32
SELECT 'public Int32 ' + name + ' { get; set; }'
FROM Sys.COLUMNS WHERE Object_ID = OBJECT_ID(@TableName)
AND System_Type_ID IN (56)
UNION
-- Int16
SELECT 'public Int16 ' + name + ' { get; set; }'
FROM Sys.COLUMNS WHERE Object_ID = OBJECT_ID(@TableName)
AND System_Type_ID IN (48, 52)
UNION
--Decimal
SELECT 'public Decimal ' + name + ' { get; set; }'
FROM Sys.COLUMNS WHERE Object_ID = OBJECT_ID(@TableName)
AND System_Type_ID IN (108, 62, 106, 60,122)
UNION
--Boolean
SELECT 'public Boolean ' + name + ' { get; set; }'
FROM Sys.COLUMNS WHERE Object_ID = OBJECT_ID(@TableName)
AND System_Type_ID IN (104)
UNION
-- DateTime
SELECT 'public DateTime ' + name + ' { get; set; }'
FROM Sys.COLUMNS WHERE Object_ID = OBJECT_ID(@TableName)
AND System_Type_ID IN (58,61)

Replace A String From The Data In The Database

Following SQL script will help to replace a particular string from all user defined tables in the database.

DECLARE @TEMP TABLE(tabname VARCHAR(100))
DECLARE @COUNT INT, @tabname VARCHAR(100)
DECLARE @ColTemp TABLE(colName VARCHAR(100))
DECLARE @ColCount INT, @ColName VARCHAR(100)
DECLARE @SQLQuery VARCHAR(1000), @ReplaceChar VARCHAR(10), @ReplaceTo VARCHAR(10)

SET @ReplaceChar='-'
SET @ReplaceTo = '?'

INSERT INTO @TEMP (tabname)
(SELECT [name] FROm SYS.TABLES WHERE name='tblFormula')

SELECT @COUNT=Count(*) FROM @TEMP

WHILE @COUNT>0
BEGIN
SELECT TOP 1 @tabname=tabname FROM @TEMP

INSERT INTO @ColTemp (colName)
(SELECT Top 1 [name] FROm SYS.COLUMNS WHERE [Object_id]=Object_ID(@tabname))

SELECT @ColCount=Count(*) FROM @ColTemp
WHILE @COLCount>0
BEGIN
SELECT TOP 1 @ColName=ColName FROM @ColTemp

SET @SQLQuery = 'UPDATE ' + @tabname + ' SET ' + @ColName + '= REPLACE(' + @ColName + ', ''' + @ReplaceChar + ''','''+ @ReplaceTo + ''')'

PRINT @SQLQuery

DELETE FROM @ColTemp WHERE ColName=@ColName
SELECT @ColCount=Count(*) FROM @ColTemp
END

DELETE FROM @TEMP WHERE tabname=@tabname
SELECT @COUNT=Count(*) FROM @TEMP
END

Get The First And Last Working Day Of Each Month For the Given Year

This store procedure will help to get the first and last working day of each month in a given year.

DECLARE @Year INT
SET @Year=2011

DECLARE @FirstDate DateTime,@LastDate DATETIME, @DayofWeek INT
DECLARE @FirstWorkingDay DATETIME, @LastWorkingDay DATETIME

SET @FirstDate = '01-01-'+CONVERT(VARCHAR(4),@Year)
SET DATEFIRST 1

WHILE (MONTH(@FirstDate) <=12 AND YEAR(@FirstDate)=@Year)
BEGIN
SET @DayofWeek = DATEPART(weekday,@FirstDate)
--First working day of the month
IF(@DayofWeek = 7)
SET @FirstWorkingDay= @FirstDate+1
ELSE IF (@DayofWeek = 6)
SET @FirstWorkingDay = @FirstDate+2
ELSE
SET @FirstWorkingDay = @FirstDate

--Get Last working day of the month
SET @LastDate=DATEADD(mm,1,@FirstDate)-1
SET @DayofWeek = DATEPART(weekday,@LastDate)
IF(@DayofWeek = 7)
SET @LastWorkingDay = @LastDate-2
ELSE IF (@DayofWeek = 6)
SET @LastWorkingDay = @LastDate-1
ELSE
SET @LastWorkingDay = @LastDate

SELECT @FirstWorkingDay AS FirstDay, @LastWorkingDay AS 'Last Day'
SET @FirstDate=DATEADD(mm,1,@FirstDate)
END

Query to Get the dependencies for given Table Name

Following Query is used to get the dependencies for the given Table.
Suppose we need to check the dependencies for the table tbl_Customer

Sample Query:

DECLARE @Objectname varchar(100)
SET @Objectname='tbl_Customer'

SELECT O1.name,O2.name FROM sys.sql_dependencies D INNER JOIN Sys.Objects O1 ON O1.object_id=D.object_id
INNER JOIN Sys.Objects O2 ON O2.object_id=D.referenced_major_id
WHERE O1.name=@Objectname OR O2.name=@Objectname

Query to get the name of the identity column for a given table

Query to get the name of the identity column for a given table.
To Get the Identity column name for Tbl_Customer we need to execute the following query.

SELECT D.name AS ColumnName, O.name AS TableName
FROM sys.identity_columns D INNER JOIN Sys.Objects O ON O.object_id=D.object_id
Where O.name='Tbl_Customer

Query to Get the Forgien Key Name Their Primary Table and Referrence Table

Following Query will give you the List of Forgien Key in a Database with Their Primary and Referrence Tables Name.

Sample Query:

SELECT F.name AS ForgienKey,O2.name AS ParentTable,O3.name AS RefTable FROM sys.foreign_keys F
INNER JOIN Sys.Objects O2 ON O2.object_id=F.parent_object_id
INNER JOIN Sys.Objects O3 ON O3.object_id=F.referenced_object_id

If you want to get forgien key for a particular table then you can use like this:

SELECT F.name AS ForgienKey,O2.name AS ParentTable,O3.name AS RefTable FROM sys.foreign_keys F
INNER JOIN Sys.Objects O2 ON O2.object_id=F.parent_object_id
INNER JOIN Sys.Objects O3 ON O3.object_id=F.referenced_object_id
Where O3.name='Tbl_User'

SQL Statement to Get the Definition of the Store Procedures

SQL Statement to Get the Definition of the System Store Procedures.

SELECT definition FROM sys.system_sql_modules

SQL Statement to Get the Definition of the User defined Store Procedures.

SELECT definition FROM sys.sql_modules

Query in SQL Server to get the Parameter list of given Store Procedure

Write a Query in SQL Server to get the Parameter list of given Store Procedure.

Suppose we want to get the name of the parameter for the Store Procedure
Course_Insert_sp then folllowing needs to be executed.

SELECT * FROM sys.parameters D INNER JOIN Sys.Objects O ON O.object_id=D.object_id WHERE O.name='Course_Insert_sp'

SQL Server Interview Questions

(1) How To Update Description Value for a Column in Table using SQL Command?
We can Update Description to Column using sp_updateextendedproperty System Store Procedure.
Sample Command to Update Description for Column in a Table:

EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N'My Description for Column Here' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTableName',       @level2type=N'COLUMN',@level2name=N'ColumnName'

(2) How To Delete Description Value for a Column in Table using SQL Command?
We can Delete Description from Column using sp_dropextendedproperty System Store Procedure.
Sample Command to Delete Description from Column in a Table:

EXEC sys.sp_dropextendedproperty @name=N'MS_Description', @value=N'My Description for Column Here' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTableName',       @level2type=N'COLUMN',@level2name=N'ColumnName'

(3) How we can add Description to the Column using Sql Command?
We can Add Description to Column using sp_addextendedproperty System Store Procedure.
Sample Command to Insert Description for Column in a Table:

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'My Description for Column Here' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTableName',       @level2type=N'COLUMN',@level2name=N'ColumnName'

(4) How To Get Description Value for all Column in Table using SQL Command?
To Get Description of Columns we need to use system function sys.fn_listextendedproperty.
Command To Get Description Data for all Columns:

SELECT * FROM   fn_listextendedproperty(NULL, 'SCHEMA',
 'dbo', 'TABLE', 'YourTable Name Here', 'COLUMN', NULL)

(5) How To Get Description Value for Single Column in Table using SQL Command?
To Get Description of Single Column we need to use system function sys.fn_listextendedproperty. we need to pass the column Name is this case.
Command To Get Description Data for Single Columns:

SELECT * FROM   fn_listextendedproperty(NULL, 'SCHEMA',
 'dbo', 'TABLE', 'Table Name Here', 'COLUMN', 'Column Name Here')

(6) How We can get the DB name using SQL Command?
Following is the Command to get the DB name using Command analyzer

SELECT DB_NAME()

(7) What is the use of Set NOCOUNT ON;?
By Default When we execute any command it return us the number of record affected. if we don't want to return the number of records affected then we can use

SET NOCOUNT ON;

Store Procedure To Create Script for Create Table

Following is the store procedure to create script for create table. this will create script for all constraint, description of columns, and index etc.

Store Procedure Script:

ALTER PROCEDURE sp_CreateTableScript

(

@TableName SYSNAME,

@IncludeConstraints BIT = 1,

@IncludeIndexes BIT = 1,

@NewTableName SYSNAME = NULL,

@UseSystemDataTypes BIT = 0

)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @MainDefinition TABLE

(

FieldValue NVARCHAR(500)

)

DECLARE @DBName SYSNAME

DECLARE @ClusteredPK BIT

DECLARE @TableSchema NVARCHAR(255)

SET @DBName = DB_NAME(DB_ID())

SELECT @TableName = name FROM sysobjects WHERE id = OBJECT_ID(@TableName)

DECLARE @ShowFields TABLE

(

FieldID INT IDENTITY(1,1),

DatabaseName VARCHAR(100),

TableOwner VARCHAR(100),

TableName VARCHAR(100),

FieldName VARCHAR(100),

ColumnPosition INT,

ColumnDefaultValue VARCHAR(100),

ColumnDefaultName VARCHAR(100),

IsNullable BIT,

DataType VARCHAR(100),

MaxLength INT,

NumericPrecision INT,

NumericScale INT,

DomainName VARCHAR(100),

FieldListingName VARCHAR(110),

FieldDefinition CHAR(1),

IdentityColumn BIT,

IdentitySeed INT,

IdentityIncrement INT,

IsCharColumn BIT

)

DECLARE @HoldingArea TABLE

(

FldID SMALLINT IDENTITY(1,1),

Flds VARCHAR(4000),

FldValue CHAR(1) DEFAULT(0)

)

DECLARE @PKObjectID TABLE

(

ObjectID INT

)

DECLARE @Uniques TABLE

(

ObjectID INT

)

DECLARE @HoldingAreaValues TABLE

(

FldID SMALLINT IDENTITY(1,1),

Flds VARCHAR(4000),

FldValue CHAR(1) DEFAULT(0)

)

DECLARE @Definition TABLE

(

DefinitionID SMALLINT IDENTITY(1,1),

FieldValue NVARCHAR(500)

)

INSERT INTO @ShowFields

(

DatabaseName,

TableOwner,

TableName,

FieldName,

ColumnPosition,

ColumnDefaultValue,

ColumnDefaultName,

IsNullable,

DataType,

MaxLength,

NumericPrecision,

NumericScale,

DomainName,

FieldListingName,

FieldDefinition,

IdentityColumn,

IdentitySeed,

IdentityIncrement,

IsCharColumn

)

SELECT

DB_NAME(),

TABLE_SCHEMA,

TABLE_NAME,

COLUMN_NAME,

CAST(ORDINAL_POSITION AS INT),

COLUMN_DEFAULT,

dobj.name AS ColumnDefaultName,

CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,

DATA_TYPE,

CAST(CHARACTER_MAXIMUM_LENGTH AS INT),

CAST(NUMERIC_PRECISION AS INT),

CAST(NUMERIC_SCALE AS INT),

DOMAIN_NAME,

COLUMN_NAME + ',','' AS FieldDefinition,

CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn,

CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed,

CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement,

CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn

FROM

INFORMATION_SCHEMA.COLUMNS c

JOIN sys.columns sc ON c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name

LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name

JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name

LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D'

WHERE c.TABLE_NAME = @TableName

ORDER BY

c.TABLE_NAME, c.ORDINAL_POSITION

SELECT TOP 1 @TableSchema = TableOwner

FROM @ShowFields

INSERT INTO @HoldingArea (Flds) VALUES('(')

INSERT INTO @Definition(FieldValue)

VALUES('CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @NewTableName ELSE @TableSchema + '.' + @TableName END)

INSERT INTO @Definition(FieldValue)

VALUES('(')

INSERT INTO @Definition(FieldValue)

SELECT

CHAR(10) + FieldName + ' ' +

CASE

WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN

DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END

ELSE UPPER(DataType) +

CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE '' END +

CASE WHEN IdentityColumn = 1 THEN

' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END +

CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END +

CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN

'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END

END +

CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END

FROM @ShowFields

IF @IncludeConstraints = 1

BEGIN

INSERT INTO @Definition(FieldValue)

SELECT

',CONSTRAINT [' + name + '] FOREIGN KEY (' + ParentColumns + ') REFERENCES [' + ReferencedObject + '](' + ReferencedColumns + ')'

FROM

(

SELECT

ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name,

REVERSE(SUBSTRING(REVERSE((

SELECT cp.name + ','

FROM

sys.foreign_key_columns fkc

JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id

WHERE fkc.constraint_object_id = fk.object_id

FOR XML PATH('')

)), 2, 8000)) ParentColumns,

REVERSE(SUBSTRING(REVERSE((

SELECT cr.name + ','

FROM

sys.foreign_key_columns fkc

JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id

WHERE fkc.constraint_object_id = fk.object_id

FOR XML PATH('')

)), 2, 8000)) ReferencedColumns

FROM sys.foreign_keys fk

) a

WHERE ParentObject = @TableName

INSERT INTO @Definition(FieldValue)

SELECT',CONSTRAINT [' + name + '] CHECK ' + definition FROM sys.check_constraints

WHERE OBJECT_NAME(parent_object_id) = @TableName

INSERT INTO @PKObjectID(ObjectID)

SELECT DISTINCT

PKObject = cco.object_id

FROM

sys.key_constraints cco

JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id

JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id

WHERE

OBJECT_NAME(parent_object_id) = @TableName AND

i.type = 1 AND

is_primary_key = 1

INSERT INTO @Uniques(ObjectID)

SELECT DISTINCT

PKObject = cco.object_id

FROM

sys.key_constraints cco

JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id

JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id

WHERE

OBJECT_NAME(parent_object_id) = @TableName AND

i.type = 2 AND

is_primary_key = 0 AND

is_unique_constraint = 1

SET @ClusteredPK = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END

INSERT INTO @Definition(FieldValue)

SELECT ',CONSTRAINT ' + name + CASE type WHEN 'PK' THEN

' PRIMARY KEY ' + CASE WHEN pk.ObjectID IS NULL THEN ' NONCLUSTERED ' ELSE ' CLUSTERED ' END

WHEN 'UQ' THEN ' UNIQUE ' END + CASE WHEN u.ObjectID IS NOT NULL THEN ' NONCLUSTERED ' ELSE '' END + '(' +

REVERSE(SUBSTRING(REVERSE((

SELECT

c.name + + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','

FROM

sys.key_constraints ccok

LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id

LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id

LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id

WHERE

i.object_id = ccok.parent_object_id AND

ccok.object_id = cco.object_id

FOR XML PATH('')

)), 2, 8000)) + ')'

FROM

sys.key_constraints cco

LEFT JOIN @PKObjectID pk ON cco.object_id = pk.ObjectID

LEFT JOIN @Uniques u ON cco.object_id = u.objectID

WHERE

OBJECT_NAME(cco.parent_object_id) = @TableName

END

INSERT INTO @Definition(FieldValue)

VALUES(')')

INSERT INTO @Definition(FieldValue)

VALUES('GO')

IF @IncludeIndexes = 1

BEGIN

INSERT INTO @Definition(FieldValue)

SELECT

ISNULL(('CREATE ' + type_desc + ' INDEX [' + [name] COLLATE SQL_Latin1_General_CP1_CI_AS + '] ON [' + OBJECT_NAME(object_id) + '] (' +

REVERSE(SUBSTRING(REVERSE((

SELECT name + CASE WHEN sc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','

FROM

sys.index_columns sc

JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id

WHERE

OBJECT_NAME(sc.object_id) = @TableName AND

sc.object_id = i.object_id AND

sc.index_id = i.index_id

ORDER BY index_column_id ASC

FOR XML PATH('')

)), 2, 8000)) + ')' ),'')

FROM sys.indexes i

WHERE

OBJECT_NAME(object_id) = @TableName

AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND type = 1 THEN 0 ELSE 1 END = 1

AND is_unique_constraint = 0

AND is_primary_key = 0

END

INSERT INTO @Definition(FieldValue)

SELECT 'EXEC sys.sp_addextendedproperty @name=N'''+name+''', @value=N'''+CONVERT(varchar(100),value)+''' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''TABLE'',@level1name=N'''+@TableName+''',

@level2type=N''COLUMN'',@level2name=N'''+objName+''''

FROM ::fn_listextendedproperty(NULL, 'SCHEMA',

'dbo', 'TABLE', @TableName, 'COLUMN', NULL) WHERE CONVERT(varchar(100),value)<>''

INSERT INTO @MainDefinition(FieldValue)

SELECT FieldValue FROM @Definition

ORDER BY DefinitionID ASC

SELECT * FROM @MainDefinition

END

How to execute the above store procedure and get the create table script:

EXEC sp_CreateTableScript 'TableName'

Create Script File For Each Store Procedure And Save into Seperate SQL File For Each

With the help of sqlcmd utility we can save the script of store procedure in separate file for each store procedure.

Name of the file will be same as the name of the store procedure.
Sample Code to Create Script and Save:

select N'sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext ' + name + N' " -h-1 -k 1 >> c:\DB\sp\' + name + N'.sql' from sys.objects where type='P'

When you will execute the above command the following output will be generated based on the number of store procedure in your database:

sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext User_Authenticate_sp " -h-1 -k 1 >> c:\DB\sp\User_Authenticate_sp.sql
sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext User_GetPassword_sp " -h-1 -k 1 >> c:\DB\sp\User_GetPassword_sp.sql
sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext sp_upgraddiagrams " -h-1 -k 1 >> c:\DB\sp\sp_upgraddiagrams.sql
sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext sp_helpdiagrams " -h-1 -k 1 >> c:\DB\sp\sp_helpdiagrams.sql
sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext sp_helpdiagramdefinition " -h-1 -k 1 >> c:\DB\sp\sp_helpdiagramdefinition.sql

Save these output data into a batch file execute this batch file. this will save the store procedure script on C:\DB\sp

Store Procedure to be Used in Custom Pagging

With the help of the below set of query we can get the number of record that we want to show to user.

For this we need to pass two parameters. first is the @RecordsToPick (Number of Records that you want to picjk for the page) and the second is @PageNumber (Page number for which you want to get the records from Database.)

USE DBName
GO
DECLARE @RecordsToPick smallint, @PageNumber smallint
SET @RecordsToPick = 10
SET @PageNumber = 2

DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = ((@PageNumber-1) * @RecordsToPick)
SET @EndRow = @StartRow + @RecordsToPick

SELECT * FROM ( SELECT UserName,UserID,City,State,Country ROW_NUMBER()OVER(ORDER BY UserID) AS RowNumber
FROM as_TblMembers) As AliasName WHERE RowNumber > @StartRow AND RowNumber <= @EndRow GO

In the above store procedure first we will get the @StartRow and @EndRow to get the number of first record and last record respectively.

ROW_NUMBER()OVER(ORDER BY UserID) AS RowNumber

ROW_NUMBER() will assign a unique number to each query order by UserID. with the help of it is easy for us to get the required result.

Create XML Data From SQL Server Using "For XML Explicit"

There are different ways to get data in XML format from SQL Server like:

• For XML Auto
• For XML RAW
• For XML Explicit

But in the first and second method we can't customize the format of XML. if we need customize XML then we need to use "For XML Explicit Method" With the Help of "For XML Explicit" we can create XML in the required format.

'Tag' and 'Parent' Column are used to determine the hierarchy of xml.
Now this code will generate a parent node name root and Session as Child of Root.

[Session!2!ID] this will create an attribute of Session Node Named ID.
and [Session!2!Notes!element] this will create a Child element of Session Named Notes.

Sample Code:
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS [Session!2!ID],
NULL AS [Session!2!Notes!element],
0 AS [root!1!Customer!hide]
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
Session.SessionID AS [Session!2!ID],
Session.Notes AS [Session!2!Notes!element],
1 AS [root!1!Customer!hide]
FROM tbl_Session AS Session WHERE Session.SessionID<20
FOR XML EXPLICIT

Parse XML Data in SQL Server 2005 without using OPENXML Method

In SQL Server 2005 we can parse the XML Data without using OPENXML Methods that was used in SQL Server 2000.

There are few methods defined in SQL Server 2005 for XML DataType like:

• nodes
• query
• value

Sample Code:

DECLARE @XmlData xml
SET @XmlData='<Library>
<Subject name="ASP.NET">
<Book ID="1">
<Author>Lakhan Pal Garg</Author>
<Title>ASP.NET Tips</Title>
<Price>$100</Price>
</Book>
<Book ID="2">
<Author>Lakhan Pal Garg</Author>
<Title>SQL Server Tips</Title>
<Price>$90</Price>
</Book>
</Subject>
<Subject name="XML">
<Book ID="3">
<Author>Peter</Author>
<Title>XSLT Tutorial</Title>
<Price>$140</Price>
</Book>
<Book ID="4">
<Author>Rihana</Author>
<Title>XML Parsing in SQL Server</Title>
<Price>$120</Price>
</Book>
</Subject>
</Library>'

select R.i.value('@ID', 'varchar(30)') [BookID],
R.i.query('Author').value('.', 'varchar(30)') [Author],
R.i.query('Title').value('.', 'varchar(30)') [Title],
R.i.query('Price').value('.', 'varchar(30)') [Price]

from @XmlData.nodes('/Library/Subject/Book') R(i)

In the above Select Statement we have used @XmlData.nodes and this will return a node list we used the Alias for this "R" and i is the index of the node. now to read the value of a attribute we can use R.i.value('@ID','INT') [BookID] here BookID is Alias name for column. and to read the value of an element that is child of Book we need to write like this R.i.query('Author').value('.','varchar(30)') [AuthorName] Author is the name of Child element of Book.

Get the Number of Days in a Month for Given Month and Year

With this sample code user can get the number of days in the Given Month and Year.

User's Input Data: '02-2009' or '02/2009'

There are two method to do this:

First Method:

DECLARE @Date varchar(20),@FirstDay varchar(2), @Seperator char(1)
SET @Date='02-2009'
SET @FirstDay='01'
SET @Seperator=SUBSTRING(@Date,3,1)
SET @Date=SUBSTRING(@Date,1,3) + @FirstDay + @Seperator + SUBSTRING(@Date,4,7)
SELECT DATEDIFF(DAY,Convert(Datetime,@Date), DATEADD(MONTH,1,Convert(Datetime,@Date))) 'Days'

Second Method:

DECLARE @Date varchar(20),@Month int, @NextMonth varchar(20), @Year varchar(5),@Seperator char(1)
DECLARE @FirstDay varchar(2)
SET @Date='02-2009'
SET @FirstDay='01'
SET @Year=SUBSTRING(@Date,4,7)
SET @Seperator=SUBSTRING(@Date,3,1)
SET @Month =Convert(int,(SUBSTRING(@Date,1,2)))+1
SET @Date=SUBSTRING(@Date,1,3)+@FirstDay+@Seperator+@Year
IF(@Month>12)
BEGIN
SET @Month=1
SET @Year=CONVERT(varchar(5), (CONVERT(int, @Year)+1))
END

SET @NextMonth=convert(varchar(2), @Month) + @Seperator + @FirstDay+@Seperator+@Year
SELECT Convert(int,((Convert(Datetime,@NextMonth)) - (Convert(Datetime,@Date))))

Output:
28 Days

Get Median value from SQL Server Table

Sample code to get the median of table data in SQL Server. Declare two variables first to get the number of rows and second to store median value.
DECLARE @Count int,@Median Float

Query to get the number of rows:
SELECT @Count=COUNT(*) FROM (
SELECT ID,COUNT(ID) AS IDCount FROM MyTable GROUP BY ID)AB

if the number of rows are even then query to get the median value is:
SELECT @Median=(SUM(Convert(float,IDCount))/2) FROM(SELECT ID,COUNT(ID)AS IDCount,ROW_NUMBER() OVER(ORDER BY COUNT(ID))AS ROW
FROM MyTable GROUP BY ID)AB
WHERE AB.Row IN ((@Count/2),(@Count/2)+1)

Query to get the median in case of odd rows:
SELECT @Median=IDCount FROM(SELECT ID,COUNT(ID)AS IDCount,ROW_NUMBER() OVER(ORDER BY COUNT(ID))AS ROW
FROM MyTable GROUP BY ID)AB
WHERE AB.Row =((@Count/2)+1)

Sample Query:
DECLARE @Count int,@Median Float
SELECT @Count=COUNT(*) FROM (
SELECT ID,COUNT(ID) AS IDCount FROM MyTable GROUP BY ID)AB

DECLARE @SQLQuery varchar(500)
IF(@Count%2=0)
BEGIN
SELECT @Median=(SUM(Convert(float,IDCount))/2) FROM(SELECT ID,COUNT(ID)AS IDCount,ROW_NUMBER() OVER(ORDER BY COUNT(ID))AS ROW
FROM MyTable GROUP BY ID)AB
WHERE AB.Row IN ((@Count/2),(@Count/2)+1)
END
ELSE
SELECT @Median=IDCount FROM(SELECT ID,COUNT(ID)AS IDCount,ROW_NUMBER() OVER(ORDER BY COUNT(ID))AS ROW
FROM MyTable GROUP BY ID)AB
WHERE AB.Row =((@Count/2)+1)
SELECT @Median

SCOPE_IDENTITY() v/s @@IDENTITY

SCOPE_IDENTITY() v/s @@IDENTITY:
Both SCOPE_IDENTITY() and @@IDENTITY will return the last identity value generated in the table. but there is some difference between the two:

SCOPE_IDENTITY() will return the Identity value generated in a table that is currently in scope.

@@IDENTITY will return the Identity value generated in a table irrespective of the scope.

For Example:
Let us suppose we have two tables named table1 & table2... and we have one trigger defined on table1 that is insert a record in table2 when new record will be inserted into table1.

in this case the Output of Both SCOPE_IDENTITY() and @@IDENTITY will be different.
SCOPE_IDENTITY() will return the identity value of table1 that is in current scope.
while @@IDENTITY will return the identity value of table2.