Script to generate the SQL Server Table with data

Transact-SQL
1

I know the name of a table. I want to generate the create script with data without right-clicking on the table. What is the Script to generate the SQL Server Table?

2

Hi,

Here you go:-

  1. Get Table Structure:-

     Create FUNCTION fn_Table_Structure (@TableName AS NVARCHAR(128) = NULL) 
 RETURNS NVARCHAR(4000)
 --select dbo.fn_Table_Structure(yourtablenamehere)
 AS
 BEGIN

 DECLARE @SQL AS NVARCHAR(4000)
 DECLARE @name NVARCHAR(128)
 DECLARE @is_nullable BIT 
 DECLARE @UK  NVARCHAR(128)
 DECLARE @IDENTITY  NVARCHAR(128)
 DECLARE @system_type_name NVARCHAR(128)
 DECLARE @collation_name NVARCHAR(128)
 DECLARE @NewLine NVARCHAR(2) = CHAR(13) + CHAR(10) -- CRLF
 DECLARE  @cols NVARCHAR(MAX) = N'';
 Declare @InputSQL varchar(max)


 DECLARE CUR_Table CURSOR LOCAL FAST_FORWARD
 FOR


     SELECT  name ,
             is_nullable ,
             system_type_name ,
             is_part_of_unique_key,
 			is_identity_column,
 			collation_name
     FROM    sys.dm_exec_describe_first_result_set(N'SELECT * FROM '+@TableName, NULL, 1)
     WHERE   is_hidden = 0
     ORDER BY column_ordinal ASC 

 OPEN CUR_Table

 FETCH NEXT FROM CUR_Table INTO @name, @is_nullable, @system_type_name, @UK,@IDENTITY,
     @collation_name

 SET @SQL = 'CREATE TABLE [' + ISNULL(@TableName, 'TableName') + '] ('
     + @NewLine

 WHILE @@FETCH_STATUS = 0
     BEGIN
         SET @SQL += @NewLine + '[' + @name + ']' + ' ' + @system_type_name  + ' ' + CASE WHEN @UK=1 THEN 'IDENTITY(1,1)' ELSE '' END + ' ' + CASE WHEN @IDENTITY=1 THEN 'constraint pk_KEY primary key' else '' end
             + CASE WHEN @collation_name IS NOT NULL
                    THEN '  COLLATE ' + @collation_name + ' '
                    ELSE ''
               END + CASE WHEN @is_nullable = 0 THEN ' NOT NULL '
                          ELSE ''
                     END + ',' 
         FETCH NEXT FROM CUR_Table INTO  @name, @is_nullable, @system_type_name, @UK,@IDENTITY,
     @collation_name
     END

 SET @SQL = LEFT(@SQL, LEN(@SQL) - 1) + @NewLine + ')'

 CLOSE CUR_Table
 DEALLOCATE CUR_Table

 RETURN @SQL
 end

  2. Execute Table Structure Function & get Insert Script:-

     CREATE PROCEDURE sp_generate_insertscripts
 (
     @TABLE_NAME VARCHAR(MAX),
     @FILTER_CONDITION VARCHAR(MAX)=''
 )
 --exec sp_generate_insertscripts 'yourtablenamehere'
 AS
 BEGIN

 SET NOCOUNT ON

 DECLARE @CSV_COLUMN VARCHAR(MAX),
 @QUOTED_DATA VARCHAR(MAX),
 @TEXT VARCHAR(MAX)

 select dbo.fn_Table_Structure(@TABLE_NAME)

 SELECT @CSV_COLUMN=STUFF
 (
 (
  SELECT ',['+ NAME +']' FROM sys.all_columns 
  WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND 
  is_identity!=1 FOR XML PATH('')
 ),1,1,''
 )

 SELECT @QUOTED_DATA=STUFF
 (
 (
  SELECT ' ISNULL(QUOTENAME('+NAME+','+QUOTENAME('''','''''')+'),'+'''NULL'''+')+'','''+'+' FROM sys.all_columns 
  WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND 
  is_identity!=1 FOR XML PATH('')
 ),1,1,''
 )

 SELECT @TEXT='SELECT ''INSERT INTO '+@TABLE_NAME+'('+@CSV_COLUMN+')VALUES('''+'+'+SUBSTRING(@QUOTED_DATA,1,LEN(@QUOTED_DATA)-5)+'+'+''')'''+' Insert_Scripts FROM '+@TABLE_NAME + @FILTER_CONDITION

 --SELECT @CSV_COLUMN AS CSV_COLUMN,@QUOTED_DATA AS QUOTED_DATA,@TEXT TEXT

 EXECUTE (@TEXT)

 SET NOCOUNT OFF

 END

Thanks.

Regards,
Micheale

3
  • Right click on the Database.
  • Select Tasks -> Generate Scripts .
  • Follow the wizard
  • select the table you want
  • before finish, click advanced... select Type of Data to Script ---select Schema and data
  • Finish
4

@svs81in