Technical Article

Script Table AS Select To

,

Step 1:

Create this proc in MASTER Database.

Step 2:

you have to add prefix sp_  name of SP.

Step 3:

Pass Database name and table name then run it

--sp_CreateSelectScriptOfTable 'TestDB','Test' 
CREATE PROCEDURE sp_CreateSelectScriptOfTable @DATABSE AS NVARCHAR(MAX)
,@TABLENNAME AS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Script AS NVARCHAR(MAX)
DECLARE @sql NVARCHAR(200)

SET @sql = 'USE'

SELECT @sql = @sql + ' ' + @DATABSE + ';'

EXEC (@sql)

SELECT @Script = '  DECLARE @COLUMNNAME AS NVARCHAR(MAX) ; 

SELECT @COLUMNNAME = STUFF(( 

    SELECT ' + CHAR(39) + ',' + CHAR(39) + ' +QUOTENAME(C.column_name) 

    FROM information_schema.columns AS C 

    WHERE C.table_name = ' + CHAR(39) + @TABLENNAME + CHAR(39) + '

    FOR XML path(' + CHAR(39) + '' + CHAR(39) + ')

    ), 1, 1,' + CHAR(39) + '' + CHAR(39) + ')  ' + CHAR(13) + ' PRINT' + CHAR(39) + 'SELECT ' + CHAR(39) + '+@COLUMNNAME + CHAR(13) +' + CHAR(39) + 'FROM ' + @TABLENNAME + CHAR(39) + ' +CHAR(13) +' + CHAR(13) + CHAR(39) + 'GO' + CHAR(39)

EXEC (@Script)
END

Rate

2 (10)

Share

Share

Rate

2 (10)