Friday, July 23, 2010

Database Script Generator

SET NOCOUNT ON

GO

-- exec sp_generate_inserts 'usr_User'

PRINT 'Using Master database'

USE master

GO

PRINT 'Checking for the existence of this procedure'

IF (SELECT OBJECT_ID('sp_generate_inserts','P')) IS NOT NULL --means, the procedure already exists

BEGIN

PRINT 'Procedure already exists. So, dropping it'

DROP PROC sp_generate_inserts

END

GO

CREATE PROC sp_generate_inserts

(

@table_name varchar(776), -- The table/view for which the INSERT statements will be generated using the existing data

@target_table varchar(776) = NULL, -- Use this parameter to specify a different table name into which the data will be inserted

@include_column_list bit = 1, -- Use this parameter to include/ommit column list in the generated INSERT statement

@from varchar(800) = NULL, -- Use this parameter to filter the rows based on a filter condition (using WHERE)

@include_timestamp bit = 0, -- Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column's data in the INSERT statement

@debug_mode bit = 0, -- If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination

@owner varchar(64) = NULL, -- Use this parameter if you are not the owner of the table

@ommit_images bit = 0, -- Use this parameter to generate INSERT statements by omitting the 'image' columns

@ommit_identity bit = 0, -- Use this parameter to ommit the identity columns

@top int = NULL, -- Use this parameter to generate INSERT statements only for the TOP n rows

@cols_to_include varchar(8000) = NULL, -- List of columns to be included in the INSERT statement

@cols_to_exclude varchar(8000) = NULL, -- List of columns to be excluded from the INSERT statement

@disable_constraints bit = 0, -- When 1, disables foreign key constraints and enables them after the INSERT statements

@ommit_computed_cols bit = 0 -- When 1, computed columns will not be included in the INSERT statement

)

AS

BEGIN

SET NOCOUNT ON

--Making sure user only uses either @cols_to_include or @cols_to_exclude

IF ((@cols_to_include IS NOT NULL) AND (@cols_to_exclude IS NOT NULL))

BEGIN

RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not use both the parameters at once',16,1)

RETURN -1 --Failure. Reason: Both @cols_to_include and @cols_to_exclude parameters are specified

END

--Making sure the @cols_to_include and @cols_to_exclude parameters are receiving values in proper format

IF ((@cols_to_include IS NOT NULL) AND (PATINDEX('''%''',@cols_to_include) = 0))

BEGIN

RAISERROR('Invalid use of @cols_to_include property',16,1)

PRINT 'Specify column names surrounded by single quotes and separated by commas'

PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_include = "''title_id'',''title''"'

RETURN -1 --Failure. Reason: Invalid use of @cols_to_include property

END

IF ((@cols_to_exclude IS NOT NULL) AND (PATINDEX('''%''',@cols_to_exclude) = 0))

BEGIN

RAISERROR('Invalid use of @cols_to_exclude property',16,1)

PRINT 'Specify column names surrounded by single quotes and separated by commas'

PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_exclude = "''title_id'',''title''"'

RETURN -1 --Failure. Reason: Invalid use of @cols_to_exclude property

END

--Checking to see if the database name is specified along wih the table name

--Your database context should be local to the table for which you want to generate INSERT statements

--specifying the database name is not allowed

IF (PARSENAME(@table_name,3)) IS NOT NULL

BEGIN

RAISERROR('Do not specify the database name. Be in the required database and just specify the table name.',16,1)

RETURN -1 --Failure. Reason: Database name is specified along with the table name, which is not allowed

END

--Checking for the existence of 'user table' or 'view'

--This procedure is not written to work on system tables

--To script the data in system tables, just create a view on the system tables and script the view instead

IF @owner IS NULL

BEGIN

IF ((OBJECT_ID(@table_name,'U') IS NULL) AND (OBJECT_ID(@table_name,'V') IS NULL))

BEGIN

RAISERROR('User table or view not found.',16,1)

PRINT 'You may see this error, if you are not the owner of this table or view. In that case use @owner parameter to specify the owner name.'

PRINT 'Make sure you have SELECT permission on that table or view.'

RETURN -1 --Failure. Reason: There is no user table or view with this name

END

END

ELSE

BEGIN

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW') AND TABLE_SCHEMA = @owner)

BEGIN

RAISERROR('User table or view not found.',16,1)

PRINT 'You may see this error, if you are not the owner of this table. In that case use @owner parameter to specify the owner name.'

PRINT 'Make sure you have SELECT permission on that table or view.'

RETURN -1 --Failure. Reason: There is no user table or view with this name

END

END

--Variable declarations

DECLARE @Column_ID int,

@Column_List varchar(8000),

@Column_Name varchar(128),

@Start_Insert varchar(786),

@Data_Type varchar(128),

@Actual_Values varchar(8000), --This is the string that will be finally executed to generate INSERT statements

@IDN varchar(128) --Will contain the IDENTITY column's name in the table

--Variable Initialization

SET @IDN = ''

SET @Column_ID = 0

SET @Column_Name = ''

SET @Column_List = ''

SET @Actual_Values = ''

IF @owner IS NULL

BEGIN

SET @Start_Insert = 'INSERT INTO ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'

END

ELSE

BEGIN

SET @Start_Insert = 'INSERT ' + '[' + LTRIM(RTRIM(@owner)) + '].' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'

END

--To get the first column's ID

SELECT @Column_ID = MIN(ORDINAL_POSITION)

FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)

WHERE TABLE_NAME = @table_name AND

(@owner IS NULL OR TABLE_SCHEMA = @owner)

--Loop through all the columns of the table, to get the column names and their data types

WHILE @Column_ID IS NOT NULL

BEGIN

SELECT @Column_Name = QUOTENAME(COLUMN_NAME),

@Data_Type = DATA_TYPE

FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)

WHERE ORDINAL_POSITION = @Column_ID AND

TABLE_NAME = @table_name AND

(@owner IS NULL OR TABLE_SCHEMA = @owner)

IF @cols_to_include IS NOT NULL --Selecting only user specified columns

BEGIN

IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_include) = 0

BEGIN

GOTO SKIP_LOOP

END

END

IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns

BEGIN

IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_exclude) <> 0

BEGIN

GOTO SKIP_LOOP

END

END

--Making sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column

IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsIdentity')) = 1

BEGIN

IF @ommit_identity = 0 --Determing whether to include or exclude the IDENTITY column

SET @IDN = @Column_Name

ELSE

GOTO SKIP_LOOP

END

--Making sure whether to output computed columns or not

IF @ommit_computed_cols = 1

BEGIN

IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsComputed')) = 1

BEGIN

GOTO SKIP_LOOP

END

END

--Tables with columns of IMAGE data type are not supported for obvious reasons

IF(@Data_Type in ('image'))

BEGIN

IF (@ommit_images = 0)

BEGIN

RAISERROR('Tables with image columns are not supported.',16,1)

PRINT 'Use @ommit_images = 1 parameter to generate INSERTs for the rest of the columns.'

PRINT 'DO NOT ommit Column List in the INSERT statements. If you ommit column list using @include_column_list=0, the generated INSERTs will fail.'

RETURN -1 --Failure. Reason: There is a column with image data type

END

ELSE

BEGIN

GOTO SKIP_LOOP

END

END

--Determining the data type of the column and depending on the data type, the VALUES part of

--the INSERT statement is generated. Care is taken to handle columns with NULL values. Also

--making sure, not to lose any data from flot, real, money, smallmomey, datetime columns

SET @Actual_Values = @Actual_Values +

CASE

WHEN @Data_Type IN ('char','varchar','nchar','nvarchar')

THEN

'COALESCE('''''''' + REPLACE(RTRIM(' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'

WHEN @Data_Type IN ('datetime','smalldatetime')

THEN

'COALESCE('''''''' + RTRIM(CONVERT(char,' + @Column_Name + ',109))+'''''''',''NULL'')'

WHEN @Data_Type IN ('uniqueidentifier')

THEN

'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM(' + @Column_Name + ')),'''''''','''''''''''')+'''''''',''NULL'')'

WHEN @Data_Type IN ('text','ntext')

THEN

'COALESCE('''''''' + REPLACE(CONVERT(char(8000),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'

WHEN @Data_Type IN ('binary','varbinary')

THEN

'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'

WHEN @Data_Type IN ('timestamp','rowversion')

THEN

CASE

WHEN @include_timestamp = 0

THEN

'''DEFAULT'''

ELSE

'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'

END

WHEN @Data_Type IN ('float','real','money','smallmoney')

THEN

'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ',2)' + ')),''NULL'')'

ELSE

'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ')' + ')),''NULL'')'

END + '+' + ''',''' + ' + '

--Generating the column list for the INSERT statement

SET @Column_List = @Column_List + @Column_Name + ','

SKIP_LOOP: --The label used in GOTO

SELECT @Column_ID = MIN(ORDINAL_POSITION)

FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)

WHERE TABLE_NAME = @table_name AND

ORDINAL_POSITION > @Column_ID AND

(@owner IS NULL OR TABLE_SCHEMA = @owner)

--Loop ends here!

END

--To get rid of the extra characters that got concatenated during the last run through the loop

SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1)

SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6)

IF LTRIM(@Column_List) = ''

BEGIN

RAISERROR('No columns to select. There should at least be one column to generate the output',16,1)

RETURN -1 --Failure. Reason: Looks like all the columns are ommitted using the @cols_to_exclude parameter

END

--Forming the final string that will be executed, to output the INSERT statements

IF (@include_column_list <> 0)

BEGIN

SET @Actual_Values =

'SELECT ' +

CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +

'''' + RTRIM(@Start_Insert) +

' ''+' + '''(' + RTRIM(@Column_List) + '''+' + ''')''' +

' +''VALUES(''+ ' + @Actual_Values + '+'')''' + ' ' +

COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')

END

ELSE IF (@include_column_list = 0)

BEGIN

SET @Actual_Values =

'SELECT ' +

CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +

'''' + RTRIM(@Start_Insert) +

' '' +''VALUES(''+ ' + @Actual_Values + '+'')''' + ' ' +

COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')

END

--Determining whether to ouput any debug information

IF @debug_mode =1

BEGIN

PRINT '/*****START OF DEBUG INFORMATION*****'

PRINT 'Beginning of the INSERT statement:'

PRINT @Start_Insert

PRINT ''

PRINT 'The column list:'

PRINT @Column_List

PRINT ''

PRINT 'The SELECT statement executed to generate the INSERTs'

PRINT @Actual_Values

PRINT ''

PRINT '*****END OF DEBUG INFORMATION*****/'

PRINT ''

END

PRINT '--INSERTs generated by ''sp_generate_inserts'' stored procedure written by Vyas'

PRINT '--Build number: 22'

PRINT '--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com'

PRINT '--http://vyaskn.tripod.com'

PRINT ''

PRINT 'SET NOCOUNT ON'

PRINT ''

--Determining whether to print IDENTITY_INSERT or not

IF (@IDN <> '')

BEGIN

PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' ON'

PRINT 'GO'

PRINT ''

END

IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)

BEGIN

IF @owner IS NULL

BEGIN

SELECT 'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'

END

ELSE

BEGIN

SELECT 'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'

END

PRINT 'GO'

END

PRINT ''

PRINT 'PRINT ''Inserting values into ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' + ''''

--All the hard work pays off here!!! You'll get your INSERT statements, when the next line executes!

EXEC (@Actual_Values)

PRINT 'PRINT ''Done'''

PRINT ''

IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)

BEGIN

IF @owner IS NULL

BEGIN

SELECT 'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'

END

ELSE

BEGIN

SELECT 'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'

END

PRINT 'GO'

END

PRINT ''

IF (@IDN <> '')

BEGIN

PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' OFF'

PRINT 'GO'

END

PRINT 'SET NOCOUNT OFF'

SET NOCOUNT OFF

RETURN 0 --Success. We are done!

END

GO

PRINT 'Created the procedure'

GO

--Mark procedure as system object

EXEC sys.sp_MS_marksystemobject sp_generate_inserts

GO

PRINT 'Granting EXECUTE permission on sp_generate_inserts to all users'

GRANT EXEC ON sp_generate_inserts TO public

SET NOCOUNT OFF

GO

PRINT 'Done'