DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)

The current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use new_reseed_value as the identity. Otherwise, the next row inserted will use new_reseed_value + 1. If the value of new_reseed_value is less than the maximum value in the identity column, error message 2627 will be generated on subsequent references to the table.

1. Consider our table having some row. Now we are deleting the table with query

DELETE FROM table_name

DBCC CHECKIDENT ('table_name', RESEED, 0)

The table will generate an identity value form 1 and not from 0.


2. Now we are truncating the table with TRUNCATE TABLE Statement.

TRUNCATE TABLE table_name

DBCC CHECKIDENT ('table_name', RESEED, 0)

The table will generate an identity value form 0.

During INSERT if there is an error, the row will not Inserted but the Identity value increased according to(IDENT_INCR) the Increment.

Methods to find table INDEX or Statistics age for better performance

Original Post

 
SELECT OBJECT_NAME(tbl.object_id),
       'Index Name'      = ind.name   ,
       'Statistics Date' = STATS_DATE(ind.object_id, ind.index_id)
FROM   SYS.INDEXES ind
       INNER JOIN SYS.TABLES tbl
       ON     tbl.OBJECT_ID = ind.object_id
WHERE  tbl.TYPE             = 'U'          
            
--USE babu;
--GO
--EXEC sp_updatestats --To update all STATISTICS in the DB

Create Parameters

DECLARE @TABLE_NAME VARCHAR(50) = 'PrameterTable'
DECLARE @TableName AS         VARCHAR(200)
DECLARE @ParamNames AS        VARCHAR(8000)
SET @TableName = 'TableTest'
SELECT @ParamNames = (COALESCE(@ParamNames + CHAR(13) + CHAR(10), '') +
       CASE
              WHEN DATA_TYPE = 'bigint'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'binary'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'Bit'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'Char'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'Date'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'DateTime'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'DateTime2'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'DateTimeOffset'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'Decimal'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'float'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'geography'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'geometry'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'hierarchyid'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'image'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'int'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'money'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'NCHAR'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'numeric'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'NVARCHAR'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'ntext'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'REAL'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'smalldatetime'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'smallint'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'smallmoney'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'sql_variant'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'text'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'time'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'timestamp'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'tinyint'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'uniqueidentifier'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'varbinary'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'varchar'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'xml'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
       END )           + ','
FROM   INFORMATION_SCHEMA.Columns
WHERE  TABLE_NAME = @TableName

SET @ParamNames = LEFT(@ParamNames, LEN(@ParamNames)-1)
PRINT @ParamNames

Re Written Query

DECLARE @TABLE_NAME VARCHAR(50) = 'ContractMaster',
@DATA_TYPE  VARCHAR(50) = 'numeric'

SELECT paremeters       = ('@' + column_name + ' ' +
CASE
WHEN data_type   = 'BIGINT'
OR     data_type = 'Bit'
OR     data_type = 'Date'
OR     data_type = 'DateTime'
OR     data_type = 'DateTime2'
OR     data_type = 'DateTimeOffset'
OR     data_type = 'geography'
OR     data_type = 'geometry'
OR     data_type = 'hierarchyid'
OR     data_type = 'image'
OR     data_type = 'smalldatetime'
OR     data_type = 'sql_variant'
OR     data_type = 'time'
OR     data_type = 'timestamp'
OR     data_type = 'uniqueidentifier'
OR     data_type = 'xml'
THEN data_type
WHEN data_type   = 'binary'
OR     data_type = 'Char'
OR     data_type = 'NCHAR'
OR     data_type = 'NVARCHAR'
OR     data_type = 'text'
OR     data_type = 'varbinary'
OR     data_type = 'varchar'
THEN data_type + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
WHEN data_type   = 'Decimal'
OR     data_type = 'money'
OR     data_type = 'numeric'
OR     data_type = 'smallmoney'
THEN data_type + '(' + CAST(numeric_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'
WHEN data_type   = 'float'
OR     data_type = 'int'
OR     data_type = 'REAL'
OR     data_type = 'smallint'
OR     data_type = 'tinyint'
THEN data_type + '(' + CAST(numeric_precision AS VARCHAR) + ')'
ELSE data_type
END) + ' ,'
FROM   information_schema.columns
WHERE  table_name = @TABLE_NAME
AND
(
CASE
WHEN
(
@DATA_TYPE IS NULL
OR     @DATA_TYPE       = ''
)
THEN 1
END       = 1
OR     data_type = @DATA_TYPE
)

Re Written Query

DECLARE @TableName AS         VARCHAR(200)
DECLARE @TableName AS         VARCHAR(200)
DECLARE @ParamNames AS        VARCHAR(8000)
DECLARE @HorizontalTabChar AS CHAR(1)
DECLARE @NewLineChar AS       CHAR(2)
SET @TableName         = 'TableTest'
SET @HorizontalTabChar = CHAR(9)
SET @NewLineChar       = CHAR(13) + CHAR(10)
SET @ParamNames        = REPLICATE(@HorizontalTabChar, 3)
SELECT @ParamNames     = @ParamNames + '@' + syscolumns.name + ' ' + t.name + CAST( (
                     CASE
                            WHEN COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Scale') IS NOT NULL
                            THEN '('  + CAST(COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Precision') AS VARCHAR) + ',' + CAST(COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Scale')AS VARCHAR) + ')'
                            ELSE '('  + CAST( COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Precision')AS VARCHAR) +')'
                     END) AS VARCHAR) + ',' + @NewLineChar + REPLICATE(@HorizontalTabChar, 3)
FROM   sysobjects
       JOIN syscolumns
       ON     sysobjects.id = syscolumns.id
       INNER JOIN systypes t
       ON     syscolumns.xusertype = t.xusertype
WHERE  sysobjects.ID               = OBJECT_ID(@TableName)
SET @ParamNames                    = LEFT(@ParamNames, LEN(@ParamNames)-6)
PRINT @ParamNames

Another One Not Much Confident

DECLARE @TABLE_NAME VARCHAR(50) 
SET @TABLE_NAME =  'tabletest'

SELECT '@' + syscolumns.name + ' ' + UPPER(systypes.name) + ' ' +
       CASE
              WHEN syscolumns.prec    IS NULL
              AND    syscolumns.scale IS NULL
              THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'
              WHEN syscolumns.prec    = 0
              AND    syscolumns.scale = 0
              THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'
              WHEN syscolumns.prec    > 0
              AND    syscolumns.scale = 0
              THEN ''--CAST(syscolumns.length AS VARCHAR)
              WHEN syscolumns.prec          = 0
              AND    syscolumns.scale IS NULL
              THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'
              WHEN syscolumns.prec          > 0
              AND    syscolumns.scale IS NULL
              THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'
              ELSE '(' + CAST(syscolumns.prec AS   VARCHAR) + ' ,' + CAST(syscolumns.scale AS VARCHAR) + ')'
       END
FROM   sysobjects
       JOIN syscolumns
       ON     sysobjects.id = syscolumns.id
       JOIN systypes
       ON     syscolumns.xtype=systypes.xtype
WHERE  sysobjects.xtype       ='U'
AND    sysobjects.name        = @TABLE_NAME