Specializations

Tuesday, January 8, 2013

how to assign table structure to table variable


Declare @tblName varchar(100)
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';
Set @tblName='hlpcontent'     --- user your table name here
SELECT @sql = @sql + ',
' + c.name + ' ' + t.name
 + CASE
  WHEN t.name LIKE '%char' OR t.name LIKE '%binary' THEN
   '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE
   CONVERT(VARCHAR(4), c.max_length/CASE WHEN t.name LIKE 'n%'
   THEN 2 ELSE 1 END) END + ')'
  WHEN t.name IN ('float') THEN
      '(' + CONVERT(VARCHAR(4), c.precision) + ')'
  WHEN t.name IN ('decimal', 'numeric') THEN
   '(' + CONVERT(VARCHAR(4), c.precision) + ','
   + CONVERT(VARCHAR(4), c.scale) + ')'
  ELSE ''
 END
 FROM sys.columns AS c
 INNER JOIN sys.types AS t
 ON c.system_type_id = t.system_type_id
 AND c.user_type_id = t.user_type_id
 WHERE c.[object_id] = OBJECT_ID(@tblName)
 ORDER BY c.column_id;

SET @sql = 'DECLARE @people TABLE (' + STUFF(@sql, 1, 1, '') + '
);';

SELECT @sql;

No comments:

Post a Comment