Link to home
Start Free TrialLog in
Avatar of Tyler
TylerFlag for United States of America

asked on

query output (script) from a stored procedure

I want to take this script and put it into a stored procedure to be used again and again. the out put would be a simple Create table script

DECLARE @SourceSchemaName SYSNAME, @SourceTableName SYSNAME, @TargetSchemaName SYSNAME, @TargetTableName SYSNAME
DECLARE @cols NVARCHAR(MAX) = N'';
DECLARE @createtable NVARCHAR(MAX);

SELECT @cols += N',' + name + ' ' + system_type_name + CHAR(13)
  FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM '+  QUOTENAME(@SourceSchemaName) + '.' + QUOTENAME(@SourceTableName), NULL, 1);

SET @cols = STUFF(@cols, 1, 1, N'');

SELECT  @createtable=N'CREATE TABLE ' + @TargetSchemaName + '.' + @TargetTableName + '(' + @cols + ');'
Avatar of Nitin Sontakke
Nitin Sontakke
Flag of India image

So? What's the question!
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Tyler, a feedback will be appreciated.
Cheers
Avatar of Tyler

ASKER

thanks  that is exactly what I was looking for.