Post reply

Stored procedure to script tables, has many options

  • January 31, 2011 at 11:14 pm

    #388747

    Comments posted to this topic are about the item Stored procedure to script tables, has many options

  • February 1, 2011 at 4:36 am

    #1280846

    Can u elaborate this clearly. I mean plz. explain how it works and what is the output. Can u give step by step?:-)

  • February 1, 2011 at 7:35 am

    #1280925

    It will be a lot easier if you were to tell me what do you want to script or create so that I can give you a sample procedure call. The procedure depends on the CLR aggregate posted with the code. By default all options are turned off for proc sp_scriptmain and all create only options and printsql turned on for proc sp_script (which calls sc_scriptmain with turned on options). Parameters have default blank values therefore they are not mandatory. The first parameter is @ObjectName therefore if no parameter name is passed and only one parameter is passed that parameter will be passed into @Objectname.

    -- The parameters below is used for filtering tables

    @ObjectName SYSNAME = NULL, -- SchemaName.TableName (if this is set the other filters ignored)

    @SchemaName SYSNAME = NULL, -- SchemaName (used in like '%test%schema%', it can be used together or not with TableName)

    @TableName SYSNAME = NULL, -- TableName(used in like '%test%table%', it can be used together or not with SchemaName)

    @TableList XML = NULL, -- xml input for specific tablename + schemaname, check procedure comments

    -- This parameters will modify the output table name

    @NewSchemaName SYSNAME = NULL,

    @NewTableName SYSNAME = NULL,

    @NewDatabaseName SYSNAME = NULL,

    @NewFileGroup SYSNAME = NULL, -- If Blank no filegroup, if null copy existing, if valid will used instead of

    existing

    @NewPartition SYSNAME = NULL, -- If Blank no partition scheme, if null copy existing, if valid will used instead of existing

    @NewDataCompression SYSNAME = NULL, -- If Blank no compression, if null copy existing, if valid will used instead of existing

    @AddNamePrefix SYSNAME = NULL,

    @ReplaceReferenceSchemaFrom SYSNAME = NULL, -- if foreign key is using this schema replace it with next schema

    @ReplaceReferenceSchemaTo SYSNAME = NULL,

    -- Below bitwise fields are self explanatory, if not set the script won't generate them

    @DropForeignKey BIT = 0,

    @DropTable BIT = 0,

    @DropDefaultConstraints BIT = 0,

    @DropIndexes BIT = 0,

    @DropCheckConstraint BIT = 0,

    @CreateTable BIT = 0, /

    @CreateDefaultConstraints BIT = 0,

    @CreatePrimaryKey BIT = 0,

    @CreateUniqueKey BIT = 0,

    @createindex BIT = 0,

    @CreateForeignKey BIT = 0,

    @CreateCheckConstraint BIT = 0,

    @IgnoreDisabledForeignKey BIT = 0,

    @IgnoreDisabledCheckConstraint BIT = 0,

    -- This parameter will be filled regardless of @printsql or @executesql

    @sql VARCHAR(MAX) = NULL OUTPUT,

    @PrintSQL BIT = 0, -- Display SQL in XML format (to overcome 8000 byte limitation)

    @ExecuteSQL BIT = 0, -- Execute the SQL

    @UseTransaction BIT = 0 -- Execute the SQL within a transaction

  • May 17, 2016 at 6:46 am

    #1879495

    Another good script, thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply