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

view plaincopy to clipboardprint?

1. DELETE FROM table_name  
2.   
3. DBCC CHECKIDENT ('table_name', RESEED, 0)  

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.

view plaincopy to clipboardprint?

1. TRUNCATE TABLE table_name  
2.   
3. DBCC CHECKIDENT ('table_name', RESEED, 0)  

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

view plaincopy to clipboardprint?

1.    
2. SELECT OBJECT_NAME(tbl.object_id),  
3.        'Index Name'      = ind.name   ,  
4.        'Statistics Date' = STATS_DATE(ind.object_id, ind.index_id)  
5. FROM   SYS.INDEXES ind  
6.        INNER JOIN SYS.TABLES tbl  
7.        ON     tbl.OBJECT_ID = ind.object_id  
8. WHERE  tbl.TYPE             = 'U'            
9.               
10. --USE babu;  
11. --GO  
12. --EXEC sp_updatestats --To update all STATISTICS in the DB  

 
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

view plaincopy to clipboardprint?

1. DECLARE @TABLE_NAME VARCHAR(50) = 'PrameterTable'  
2. DECLARE @TableName AS         VARCHAR(200)  
3. DECLARE @ParamNames AS        VARCHAR(8000)  
4. SET @TableName = 'TableTest'  
5. SELECT @ParamNames = (COALESCE(@ParamNames + CHAR(13) + CHAR(10), '') +  
6.        CASE  
7.               WHEN DATA_TYPE = 'bigint'  
8.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
9.               WHEN DATA_TYPE = 'binary'  
10.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'  
11.               WHEN DATA_TYPE = 'Bit'  
12.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
13.               WHEN DATA_TYPE = 'Char'  
14.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'  
15.               WHEN DATA_TYPE = 'Date'  
16.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
17.               WHEN DATA_TYPE = 'DateTime'  
18.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
19.               WHEN DATA_TYPE = 'DateTime2'  
20.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
21.               WHEN DATA_TYPE = 'DateTimeOffset'  
22.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
23.               WHEN DATA_TYPE = 'Decimal'  
24.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'  
25.               WHEN DATA_TYPE = 'float'  
26.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'  
27.               WHEN DATA_TYPE = 'geography'  
28.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
29.               WHEN DATA_TYPE = 'geometry'  
30.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
31.               WHEN DATA_TYPE = 'hierarchyid'  
32.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
33.               WHEN DATA_TYPE = 'image'  
34.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
35.               WHEN DATA_TYPE = 'int'  
36.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'  
37.               WHEN DATA_TYPE = 'money'  
38.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'  
39.               WHEN DATA_TYPE = 'NCHAR'  
40.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'  
41.               WHEN DATA_TYPE = 'numeric'  
42.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'  
43.               WHEN DATA_TYPE = 'NVARCHAR'  
44.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'  
45.               WHEN DATA_TYPE = 'ntext'  
46.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(character_maximum_length AS VARCHAR) + ')'  
47.               WHEN DATA_TYPE = 'REAL'  
48.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'  
49.               WHEN DATA_TYPE = 'smalldatetime'  
50.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
51.               WHEN DATA_TYPE = 'smallint'  
52.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'  
53.               WHEN DATA_TYPE = 'smallmoney'  
54.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'  
55.               WHEN DATA_TYPE = 'sql_variant'  
56.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
57.               WHEN DATA_TYPE = 'text'  
58.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'  
59.               WHEN DATA_TYPE = 'time'  
60.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
61.               WHEN DATA_TYPE = 'timestamp'  
62.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
63.               WHEN DATA_TYPE = 'tinyint'  
64.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'  
65.               WHEN DATA_TYPE = 'uniqueidentifier'  
66.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
67.               WHEN DATA_TYPE = 'varbinary'  
68.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'  
69.               WHEN DATA_TYPE = 'varchar'  
70.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'  
71.               WHEN DATA_TYPE = 'xml'  
72.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
73.        END )           + ','  
74. FROM   INFORMATION_SCHEMA.Columns  
75. WHERE  TABLE_NAME = @TableName  
76.   
77. SET @ParamNames = LEFT(@ParamNames, LEN(@ParamNames)-1)  
78. PRINT @ParamNames  

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

view plaincopy to clipboardprint?

1. DECLARE @TABLE_NAME VARCHAR(50) = 'ContractMaster',  
2. @DATA_TYPE  VARCHAR(50) = 'numeric'  
3.   
4. SELECT paremeters       = ('@' + column_name + ' ' +  
5. CASE  
6. WHEN data_type   = 'BIGINT'  
7. OR     data_type = 'Bit'  
8. OR     data_type = 'Date'  
9. OR     data_type = 'DateTime'  
10. OR     data_type = 'DateTime2'  
11. OR     data_type = 'DateTimeOffset'  
12. OR     data_type = 'geography'  
13. OR     data_type = 'geometry'  
14. OR     data_type = 'hierarchyid'  
15. OR     data_type = 'image'  
16. OR     data_type = 'smalldatetime'  
17. OR     data_type = 'sql_variant'  
18. OR     data_type = 'time'  
19. OR     data_type = 'timestamp'  
20. OR     data_type = 'uniqueidentifier'  
21. OR     data_type = 'xml'  
22. THEN data_type  
23. WHEN data_type   = 'binary'  
24. OR     data_type = 'Char'  
25. OR     data_type = 'NCHAR'  
26. OR     data_type = 'NVARCHAR'  
27. OR     data_type = 'text'  
28. OR     data_type = 'varbinary'  
29. OR     data_type = 'varchar'  
30. THEN data_type + '(' + CAST(character_maximum_length AS VARCHAR) + ')'  
31. WHEN data_type   = 'Decimal'  
32. OR     data_type = 'money'  
33. OR     data_type = 'numeric'  
34. OR     data_type = 'smallmoney'  
35. THEN data_type + '(' + CAST(numeric_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'  
36. WHEN data_type   = 'float'  
37. OR     data_type = 'int'  
38. OR     data_type = 'REAL'  
39. OR     data_type = 'smallint'  
40. OR     data_type = 'tinyint'  
41. THEN data_type + '(' + CAST(numeric_precision AS VARCHAR) + ')'  
42. ELSE data_type  
43. END) + ' ,'  
44. FROM   information_schema.columns  
45. WHERE  table_name = @TABLE_NAME  
46. AND  
47. (  
48. CASE  
49. WHEN  
50. (  
51. @DATA_TYPE IS NULL  
52. OR     @DATA_TYPE       = ''  
53. )  
54. THEN 1  
55. END       = 1  
56. OR     data_type = @DATA_TYPE  
57. )  

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

view plaincopy to clipboardprint?

1. DECLARE @TableName AS         VARCHAR(200)  
2. DECLARE @TableName AS         VARCHAR(200)  
3. DECLARE @ParamNames AS        VARCHAR(8000)  
4. DECLARE @HorizontalTabChar AS CHAR(1)  
5. DECLARE @NewLineChar AS       CHAR(2)  
6. SET @TableName         = 'TableTest'  
7. SET @HorizontalTabChar = CHAR(9)  
8. SET @NewLineChar       = CHAR(13) + CHAR(10)  
9. SET @ParamNames        = REPLICATE(@HorizontalTabChar, 3)  
10. SELECT @ParamNames     = @ParamNames + '@' + syscolumns.name + ' ' + t.name + CAST( (  
11.                      CASE  
12.                             WHEN COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Scale') IS NOT NULL  
13.                             THEN '('  + CAST(COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Precision') AS VARCHAR) + ',' + CAST(COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Scale')AS VARCHAR) + ')'  
14.                             ELSE '('  + CAST( COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Precision')AS VARCHAR) +')'  
15.                      END) AS VARCHAR) + ',' + @NewLineChar + REPLICATE(@HorizontalTabChar, 3)  
16. FROM   sysobjects  
17.        JOIN syscolumns  
18.        ON     sysobjects.id = syscolumns.id  
19.        INNER JOIN systypes t  
20.        ON     syscolumns.xusertype = t.xusertype  
21. WHERE  sysobjects.ID               = OBJECT_ID(@TableName)  
22. SET @ParamNames                    = LEFT(@ParamNames, LEN(@ParamNames)-6)  
23. PRINT @ParamNames  

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

view plaincopy to clipboardprint?

1. DECLARE @TABLE_NAME VARCHAR(50)   
2. SET @TABLE_NAME =  'tabletest'  
3.   
4. SELECT '@' + syscolumns.name + ' ' + UPPER(systypes.name) + ' ' +  
5.        CASE  
6.               WHEN syscolumns.prec    IS NULL  
7.               AND    syscolumns.scale IS NULL  
8.               THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'  
9.               WHEN syscolumns.prec    = 0  
10.               AND    syscolumns.scale = 0  
11.               THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'  
12.               WHEN syscolumns.prec    > 0  
13.               AND    syscolumns.scale = 0  
14.               THEN ''--CAST(syscolumns.length AS VARCHAR)  
15.               WHEN syscolumns.prec          = 0  
16.               AND    syscolumns.scale IS NULL  
17.               THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'  
18.               WHEN syscolumns.prec          > 0  
19.               AND    syscolumns.scale IS NULL  
20.               THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'  
21.               ELSE '(' + CAST(syscolumns.prec AS   VARCHAR) + ' ,' + CAST(syscolumns.scale AS VARCHAR) + ')'  
22.        END  
23. FROM   sysobjects  
24.        JOIN syscolumns  
25.        ON     sysobjects.id = syscolumns.id  
26.        JOIN systypes  
27.        ON     syscolumns.xtype=systypes.xtype  
28. WHERE  sysobjects.xtype       ='U'  
29. AND    sysobjects.name        = @TABLE_NAME  

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