SQL SERVER – 2008 – 2008 R2 – Create Script to Copy Database Schema and All The Objects – Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects

Quite often I get the request regarding how to copy all the objects – including schema and data from any database and re-create it on another instance.

SQL Server 2008 and SQL Server 2008 R2 has script generator wizard which does it for us. I ask you to pay special attention to image #5.

After the script is generated, the next challenge often users face is how to execute this large script as SQL Server Management Studio does not open the file. One can use SQLCMD for the same. See that in the last image of this post.

Pay attention to the option Types of data to script – select option ‘Schema and data’

As the file with data will be very large, use SQLCMD to execute the large script which will create database with schema & data.

Please let me know if you have any question or suggestion on this blog post.

Watch SQL in Sixty Seconds Episode on same subject.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

86 thoughts on “SQL SERVER – 2008 – 2008 R2 – Create Script to Copy Database Schema and All The Objects – Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects

    • We are planning to move databases from SQL 2003 to SQL 2008 using detach and attach procedure. Please guide me the full procedure including the logins on source to be created on destination server.

  1. What I personally prefer as a method of transferring schema objects and data across databases or even across instances is the scripting method above together with the SSIS.
    The method of scripting you mention above is quite good, and I use it to generate the script of the schema. It is very flexible, since I get to chose which objects and what granularity of detail to script. The scripting in SSMS also checks for dependencies and scripts the objects in a particular order.
    For moving data, though, I find it very clumsy, especially with larger amount of data.
    The best way to do it is to script the schema and then use SSIS to load the data. SSIS is very easy to use and it offers isolation and lock handling which speed up the process of the data loading.

    Feodor

  2. @Sunil: the scripting of data is new for SQL 2008. In SQL 2005 you can script only the schema. Scripting the schema is sufficient, though; look at my comment above and you will see that you can use the scripted schema and then SSIS to move the data.

    Feodor

  3. Hi,
    This is good way but if you have altered the table those schema is not coming when you use Generate Scripts. I’m using this only for generating the schema but many times the table that i altered are skipped(i.e. new columns or datatype changes are not reflected). I don’t know the reason But whethere I go to Design editor and alter or use command its not reflected. Have any of you faced this problem???

  4. All,

    I have generated .sql scripts from both the Studio Express tool as well as from a dos prompt and now I’m creating a .bat file to execute the .sql scripts. One script drops the database, the next script (schema/database) creates the database and tables and the last script is a data only script to insert the data back into the database/tables, when I execute the .bat the scripts all run, the database gets dropped, database & tables get created and it appears the data load script runs however the tables don’t get populated, I created a bat_log.txt file that captured the dos stuff and in there it tells me that XX rows affected (the xx number matches the number of rows I expect in each tables) however the data doesn’t get into the database tables, any ideas why?

    thanks,

    ~Scott.

  5. We just perform a backup of the source db & then restore it to the target server.

    About all that’s left to do then is clean up the list of user accounts and reset permissions.

    It’s not especially elegant, but it’s fast, and it works every time.

    Mike

  6. I am using SQL Server 2008 but the Choose Objects page in Generate Scripts does not display DDL Triggers as shown above, and these are what I want to script. Any ideas??

  7. I have learned something new, and this is very good as far as it went. However, I’m missing something. I used the generate script to copy the users and schemas for one DB on a server to a clone on another server. I used the drop and create option. After it executed, the hundreds of securables for the users where I generated the script are missing from my target database.How can I copy this data for the database users?

  8. I am working on an instance of SQL Server Standard Edition and the Script Wizard Choose Object Types screen only has Tables and Users. The other object types are not there to be selected. Any idea why this is?

  9. Hi Pinal,
    I am genertaing Sql Script for all objects like Tables, Storedprocedure,Triggers, Function etc. Its Generating Scuccessfully and showing all the objects as scuccess in the final report but some of the tables are missing in the script. I had selected all the objects and aslo showing those objects scuccess in the report. Can u please Help me to find the reason that why this happening.

    • Hello, this is happening to me too :(.
      Dear Pinal, do you know why could it happen? We are dealing with a big database and many objects are missing :(

  10. Hello,
    I’m doing it to transfer a db from sql 2008 to 2005.
    The file with the datas is to large to be executed in sql 2005.
    Even with sql cmd it’s the same result, not working.
    What should i do?

  11. hello all,

    I just want to take complete backup of databases as above process is doing.
    But the problem is I want it by command so that i can use it in batch to schedule it. Please let me know hot to set that .

    • Anyone has the answer to this question? I.e. Have scripts instead of GUI so the job of creating schema scripts and/or data scripts can be automated.

  12. Pinal in reading this comment listing I found some fairly significant questions that I would like the answer to.

    Is there anyway you could take the time to go through these comments and answer some of these questions?

  13. Pinal
    This was perfect – I find myself hitting your blogs many times when I have SQL questions – thanks a million for taking the effort to post this items.

  14. Hi
    I am using ms sql server R2 2008 .For generating script from remote server(2005) to my local server(2008) ,i have used the approach u told here,
    but getting an error while saving to new query window since data is bulk i guess.
    This is the error
    System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
    at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e)
    at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
    at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

  15. The company I work for is doing data migration from SQL server 2005 to 2008 R2 Enterprise. The former DBA sent me the generated schema for the database in a .sql file, which I can open and see in SSMS. The DBA also sent me the tables in .dat format.
    How can I have the tables transfer to the new 2008 R2 with the schema of the original 2005?

    Thanks a lot for your help.

  16. Hi Pinal,

    It will generate whole scripts which also includes tables from other databases which depends the current selected DB.

    Choose “Select Specific database objects” option, (3rd image)

    Thanks for this post.

  17. Hello everyone!
    I have an issue doing this… the export of the objects works fine (I’m doing it without data) but when i open the file in a text editor i can’t find some objects that the wizard said was exported… Any suggestions?

  18. perfect
    Iam doing it to transfer db from device to device .but how to restore this script file to the second device please help me .
    thanks

  19. HI pinal,
    I am getting “Login failed” error , when i run the script on cmd.

    Please , help me.

    Thanks in advance.

  20. I have recently started learning SQL, and was instructed by DBA that you can not export the sysdiagrams, that with 2005 it just breaks it and with 2008 r2, it is related to the way that the SQL translates the sysdiagram upon exporting, where it is into an excel or flat-file(which is all I am experienced on). They database diagram is what officially makes the database a relational database(is the way I understand it), so my question is why can I not or how can I export they sysdiagrams along with the tables? If it can’t be done via excel or flat-file, then why? I am just wanted to understand why every time I am doing this, I must recreate the relationships between PK, CPK, and FKs. It would be so convienant if I could just export the sysdiagrams along with them and then not be forced to re-invent the wheel everytime.

    So I was told, sysdiagrams can’t be exported and imported by excel or flat-file, but why is this? All that was explained was it has to do with the way the sql code translates xml(what’s that even got to do with it). xml is scripting, so I am not seeing the big picture.

    Please advise. I want to make sure I understand completely how and why this is not possible?

  21. How to deny permission to user for generating scripts.I want to restrict some users so that they cannot generate the script.

  22. The output sql file is huge in size. It is taking hrs to run. May we not take multiple .sql files – one after another? Any 3rd party tool for this?

  23. Hi All
    Does anyone know how to create script for a Master Data Services Database. SInce there are complicated relationships between the tables , I am not able to execute the generated script. Basically I am trying to change collations, so generating the script and then changing the collation would perhaps help. Any ideas guys
    Cheers
    Nithin

  24. Pingback: SQL SERVER – Generate Script for Schema and Data – SQL in Sixty Seconds #021 – Video « SQL Server Journey with SQL Authority

  25. I used ”SQL Server 2008 – Developer Edition” for a few year only for use the ”SQL Server Management Studio” and the ”Import and Export Data” tool, no SQL Server needed becouse I always use remote server.

    Now, I tryed the ”SQL Server Management Studio Express 2012”, it have the ”Import and Export Data” tool, just what I need but now I have a issue, when I do a right-click on a database to access the ”Generate Scripts” wizard I receive the following message:

    Cannot open database ”msdb” requested by the login. The login failed.
    Login failed for user ’stocks’. (Microsoft SQL Server, Error: 4060)

    I never had this issue with SQL Server 2008, 2005 and 2000. Anyone knows why the login to database ”msdb” is required to do this action? Is there any alternate method to access the ”Generate Scripts” wizard?

  26. Some options were different and some were as it is as shown in this page, when i create script. But finally i was successsful in creating script.

  27. What privileges on what objects are required to generate script of a database?
    I tried to generate a database on which I am having db_owner privilege but failed!!
    What is the workaround for that?

  28. Hi,
    I’m using the same method to transfer a table with more than 13M records to another database on another server. the problem is there is a prompt message for each 100 records saying Processed ???? total records and it causes the process take too long. on the other hand, since the script file is too big, it’s not possible to open it with an editor and remove that message from the script. Is there any way to create such script without that prompt message? Or use another SQL script to insert the records as a bulk copy or something?

    Thank you
    Farhad

  29. I’m using a MSSQL 2008 (Microsoft SQL Server Management Studio 10.0.1600.22 ((SQL_PreRelease).080709-1414 )). When I use TASK > GENERATE SCRIPTS I get a slightly different interface and at IMAGE 5 I don’t get the option to choose advanced (though I see these options on a previous screen) and there is no options to change TYPE OF DATA TO SCRIPT. Any clues?

  30. Hi
    i have some data with date filed in it so when i generate the insert script the date field values coming like this CAST(0x22250B00 AS Date any idea how to tackle this.

  31. I know this is a dumb question, but I want to use this for a test DB on the same server it came from. I DO NOT want to create the same DB. Can I change the name of the script, or is there a way to point it to the DB I want to put it in?
    Maybe my mistake was taking the schemas. Tables, data and procs is really all I needed. Any input is welcome!

  32. I am copying a Database between two servers, where the Source server is on a webhotel, so I only can use scripting. I been struggling for days with this. But so fare I succeed to copy most, but are missing the DATABASE DIAGRAM ??? How do I make sure the DIAGRAM will follow in the copy??

  33. Hello, I want to take a 2012 SQL Express Database and convert it to SQL 2008 Database. Is this possible and is it done the same way as you mentioned above?

  34. Without wizard you can import/export data/schema..
    ——————————————————————

    CREATE PROCEDURE [dbo].[sp_Table_Data_Script]
    @SchemaName VARCHAR(MAX),
    @TableName VARCHAR(Max),
    @WhereClause NVARCHAR(Max),
    @TopNo INT
    AS

    DECLARE @SQL VARCHAR(max)
    DECLARE @nSQL NVARCHAR(Max)
    DECLARE @RecordCount INT
    DECLARE @FirstColumn NVARCHAR(Max)

    –SET @TableName=QUOTENAME(@TableName) — Quote the table name
    SET @TableName =@SchemaName + ‘.’ + QUOTENAME(@TableName)
    Print @TableName

    /*We need to find the record count in order to remove Union ALL from the last row*/
    SET @nSQL=”
    SET @nSQL= N’SELECT @RecordCount=COUNT(*) FROM ‘ + @TableName + (CASE WHEN ISNULL(@Whereclause,”) ” THEN ‘ Where ‘ + @Whereclause ELSE ” END)
    Print @nSQL
    EXEC sp_executesql @query = @nSQL, @params = N’@RecordCount INT OUTPUT’, @RecordCount = @RecordCount OUTPUT
    /*****************************************************************/

    /*Need to check either top No of record is less than record count
    in order to remove union all from the last row */
    IF @TopNo IS NOT NULL And @TopNo<@RecordCount
    BEGIN
    SET @RecordCount=@TopNo
    END
    /***************************************************************/
    /*** Here we need to find the first column name to generate a serial number and insert an "Insert into statement" in the first row ***/
    SET @nSQL=''
    SET @nSQL= N'SELECT @FirstColumn=[name] FROM sys.columns WHERE [Column_id]=1 And object_ID=object_ID('''+ @TableName + ''')'
    Print @nSQL
    EXEC sp_executesql @query = @nSQL, @params = N'@FirstColumn nvarchar(Max) OUTPUT', @FirstColumn = @FirstColumn OUTPUT
    /**************************************************************/

    DECLARE @FieldName VARCHAR(max)
    SET @FieldName=''

    If (Select Count(*) FROM sys.columns WHERE object_id=object_id('' + @TableName + '') And is_identity0)=1

    BEGIN
    SET @FieldName = STUFF(
    (
    SELECT ‘,’ + QUOTENAME([Name]) FROM sys.columns WHERE object_id=object_id(” + @TableName + ”) Order By [column_id]
    FOR XML PATH(”)), 1, 1, ”)
    Set @FieldName ='(‘ + @FieldName + ‘)’
    Print @FieldName
    Print len(@FieldName)
    END

    /*******Create list of comma seperated columns *******/
    SET @SQL= (SELECT STUFF((SELECT(CASE
    WHEN system_type_id In (167,175,189) THEN + ‘ Cast(ISNULL(LTRIM(RTRIM(”N”””+Replace(‘ + QUOTENAME([Name])+ ‘,””””,””””””)+””””’+ ‘)),”NULL”) as varchar(max)) + ” AS ‘ + QUOTENAME([Name]) + ”’ + ” ,”’+’+ ‘
    WHEN system_type_id In (231,239) THEN + ‘ Cast(ISNULL(LTRIM(RTRIM(”N”””+Replace(‘ + QUOTENAME([Name])+ ‘,””””,””””””)+””””’+ ‘)),”NULL”) as nvarchar(max)) + ” AS ‘ + QUOTENAME([Name]) + ”’ + ” ,”’+’+ ‘
    WHEN system_type_id In (58,61,36) THEN + ‘ ISNULL(LTRIM(RTRIM(”N””” + Cast(‘ + QUOTENAME([Name])+ ‘ as varchar(max))+””””’ + ‘)),”NULL”) + ” AS ‘ + QUOTENAME([Name]) + ”’+ ” ,”’+’ + ‘
    WHEN system_type_id In (48,52,56,59,60,62,104,106,108,122,127) THEN + ‘ ISNULL(Cast(‘ + QUOTENAME([Name])+ ‘ as varchar(max)),”NULL”)+ ” AS ‘ + QUOTENAME([Name]) + ”’ + ” ,”’+’+ ‘
    END
    )
    FROM
    sys.columns WHERE object_ID=object_ID(”+ @TableName + ”) FOR XML PATH(”)),1,1,’ ‘))

    /*******************************************************/

    /* Here 500 means if the record count is 500 or top no 500 then it will generate “Insert into select ..Union All ”
    Because more than 500 might reduce its performance. */
    IF @TopNo <500 or @RecordCount<500
    BEGIN
    IF @TopNo IS NULL
    BEGIN
    SET @SQl='SELECT (Case When ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@FirstColumn) + ') =1 THEN '' INSERT INTO ' + @TableName + ' ' + ' '+ @FieldName + ' ''' + ' ELSE '''' END) + ''SELECT ''+'
    + Left(@SQL,Len(@SQL)-8) + ' + (CASE WHEN ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@FirstColumn) + ') ‘ + CONVERT(VARCHAR(10),@RecordCount)
    + ‘ THEN ” UNION ALL” ELSE ”” END) AS [Name] ‘ + ‘ FROM ‘ + @TableName +(CASE WHEN ISNULL(@Whereclause,”) ” THEN ‘ WHERE ‘ + @Whereclause ELSE ” END)
    END
    ELSE
    BEGIN
    SET @SQl= ‘SELECT TOP ‘ + CONVERT(VARCHAR(10),@TopNo) + ‘ (CASE WHEN ROW_NUMBER() OVER (ORDER BY ‘ + QUOTENAME(@FirstColumn) + ‘) =1 THEN ” INSERT INTO ‘
    + @TableName + ‘ ‘ + ‘ ‘+ @FieldName + ”” + ‘ ELSE ”” END) + ”SELECT ”+’ + LEFT(@SQL,LEN(@SQL)-8) + ‘ + (CASE WHEN ROW_NUMBER() OVER (ORDER BY ‘ + QUOTENAME(@FirstColumn) + ‘) ‘
    + CONVERT(VARCHAR(10),@RecordCount) + ‘ THEN ” Union All” ELSE ”” END) AS [Name]‘ + ‘ FROM ‘ + @TableName + (CASE WHEN ISNULL(@Whereclause,”) ” THEN ‘ WHERE ‘ + @Whereclause ELSE ” END)
    END
    END
    ELSE
    — Greator then 500 will generate “insert into select *” … for each record.
    BEGIN
    IF @TopNo IS NULL
    BEGIN
    SET @SQl=’SELECT ” INSERT INTO ‘ + @TableName + ‘ ‘ + ‘ ‘+ @FieldName + ‘ ” + ”SELECT ”+’ + Left(@SQL,Len(@SQL)-8)
    + ‘ AS [Name] ‘ + ‘ FROM ‘ + @TableName +(CASE WHEN ISNULL(@Whereclause,”) ” THEN ‘ WHERE ‘ + @Whereclause ELSE ” END)
    END
    ELSE
    BEGIN
    SET @SQl=’SELECT TOP ‘ + CONVERT(VARCHAR(10),@TopNo) + ”’ INSERT INTO ‘ + @TableName + ‘ ‘ + ‘ ‘+ @FieldName
    + ‘ ” + ”SELECT ”+’ + Left(@SQL,Len(@SQL)-8) + ‘ AS [Name] ‘ + ‘ FROM ‘ + @TableName +(CASE WHEN ISNULL(@Whereclause,”) ” THEN ‘ WHERE ‘ + @Whereclause ELSE ” END)
    END
    END
    EXEC (@SQL)

    GO

    CREATE PROCEDURE [dbo].[spS_DatabaseInformationGet]
    @getValue VARCHAR(50) = NULL
    AS

    SET NOCOUNT ON;

    IF @getValue = ‘DATABASE’
    BEGIN
    SELECT [name]
    FROM sys.databases
    END
    ELSE IF @getValue = ‘TABLE’
    BEGIN
    declare @TableList TABLE
    (
    name nvarchar(max)
    )
    DECLARE @name VARCHAR(128),@setvalgo varchar(5)
    SET @setvalgo = ‘GO’
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = ‘U’ AND category = 0 ORDER BY [name])
    WHILE @name IS NOT NULL
    BEGIN
    insert into @TableList
    SELECT ‘IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N”’ +RTRIM(@name)+”’) AND type in (N”U”)) DROP TABLE ‘+RTRIM(@name)
    –SELECT ‘DROP TABLE [dbo].[' + RTRIM(@name) +']‘
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = ‘U’ AND category = 0 AND [name] > @name ORDER BY [name])
    insert into @TableList
    select @setvalgo
    END

    insert into @TableList
    SELECT ‘create table [' + so.name + '] (‘ + o.list + ‘)’ + CASE WHEN tc.Constraint_Name IS NULL THEN ” ELSE ‘ALTER TABLE ‘ + so.Name + ‘ ADD CONSTRAINT ‘ + tc.Constraint_Name + ‘ PRIMARY KEY ‘ + ‘ (‘ + LEFT(j.List, Len(j.List)-1) + ‘)’ END
    as name
    from sysobjects so
    cross apply
    (SELECT
    ‘ ['+column_name+'] ‘ +
    data_type + case data_type
    when ‘sql_variant’ then ”
    when ‘text’ then ”
    when ‘decimal’ then ‘(‘ + cast(numeric_precision_radix as varchar) + ‘, ‘ + cast(numeric_scale as varchar) + ‘)’
    else coalesce(‘(‘+case when character_maximum_length = -1 then ‘MAX’ else cast(character_maximum_length as varchar) end +’)’,”) end + ‘ ‘ +
    case when exists (
    select id from syscolumns
    where object_name(id)=so.name
    and name=column_name
    and columnproperty(id,name,’IsIdentity’) = 1
    ) then
    ‘IDENTITY(‘ +
    cast(ident_seed(so.name) as varchar) + ‘,’ +
    cast(ident_incr(so.name) as varchar) + ‘)’
    else ”
    end + ‘ ‘ +
    (case when IS_NULLABLE = ‘No’ then ‘NOT ‘ else ” end ) + ‘NULL ‘ +
    case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN ‘DEFAULT ‘+ information_schema.columns.COLUMN_DEFAULT ELSE ” END + ‘, ‘
    from information_schema.columns where table_name = so.name
    order by ordinal_position
    FOR XML PATH(”)) o (list)
    left join
    information_schema.table_constraints tc
    on tc.Table_name = so.Name
    AND tc.Constraint_Type = ‘PRIMARY KEY’
    cross apply
    (select ‘[' + Column_Name + '], ‘
    FROM information_schema.key_column_usage kcu
    WHERE kcu.Constraint_Name = tc.Constraint_Name
    ORDER BY
    ORDINAL_POSITION
    FOR XML PATH(”)) j (list)
    where xtype = ‘U’
    AND name NOT IN (‘dtproperties’)
    select * from @TableList
    END
    ELSE IF @getValue = ‘TABLE_DATA’
    BEGIN
    DECLARE @TableName VARCHAR(1000)
    DECLARE @TableDataRow BIGINT
    DECLARE @TabIdenOn varchar(100)
    DECLARE @TabIdenOff varchar(100)
    create table #temp(name varchar(MAX))
    –Cursor
    DECLARE @getTable CURSOR
    SET @getTable = CURSOR FOR
    SELECT TABLE_NAME
    FROM information_Schema.tables
    OPEN @getTable
    FETCH NEXT
    FROM @getTable INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SELECT @TableDataRow=rows FROM sys.partitions
    WHERE object_id = object_id(@TableName)
    AND index_id 0
    BEGIN
    IF ((SELECT OBJECTPROPERTY( OBJECT_ID(N”+@TableName+”), ‘TableHasIdentity’)) = 1)
    BEGIN
    set @TabIdenOn = ‘SET IDENTITY_INSERT ‘ + @TableName + ‘ ON’
    set @TabIdenOff = ‘SET IDENTITY_INSERT ‘ + @TableName + ‘ OFF’
    insert into #temp(name)
    select @TabIdenOn
    insert into #temp(name)
    exec sp_Table_Data_Script ”,@TableName,NULL,NULL
    insert into #temp(name)
    select @TabIdenOff
    END
    ELSE
    BEGIN
    insert into #temp(name)
    exec sp_Table_Data_Script ”,@TableName,NULL,NULL
    END

    END
    FETCH NEXT
    FROM @getTable INTO @TableName
    END
    CLOSE @getTable
    DEALLOCATE @getTable

    SELECT name
    FROM #temp
    DROP TABLE #temp
    END
    ELSE IF @getValue = ‘P_F_T_V_SCRIPT’
    BEGIN
    –SELECT object_definition(object_id) AS name
    –FROM sys.objects
    –WHERE type_desc in (‘SQL_SCALAR_FUNCTION’,
    — ‘SQL_STORED_PROCEDURE’,
    — ‘SQL_TABLE_VALUED_FUNCTION’,
    — ‘SQL_TRIGGER’,
    — ‘VIEW’)

    DECLARE @ProcName VARCHAR(200),@FunName VARCHAR(200),@PExist VARCHAR(MAX)
    DECLARE @MyCursor CURSOR,@MyCursorSPS CURSOR,@MyCursorFUN CURSOR,@MyCursorFUNS CURSOR
    DECLARE @setval1 VARCHAR(25),@setval2 VARCHAR(5),@setval3 VARCHAR(25),@setval4 VARCHAR(5)
    SET @setval1=’SET ANSI_NULLS ON ‘
    SET @setval2=’GO’
    SET @setval3=’SET QUOTED_IDENTIFIER ON’

    DECLARE @StoredProcsList TABLE
    (
    name TEXT
    )
    –Add Go
    INSERT INTO @StoredProcsList
    SELECT @setval2

    DECLARE @SPCHECKEXIST CURSOR
    SET @SPCHECKEXIST = CURSOR FOR
    –Select
    SELECT name FROM sys.objects WHERE type = ‘P’
    OPEN @SPCHECKEXIST
    FETCH NEXT
    FROM @SPCHECKEXIST INTO @ProcName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @PExist= ‘IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N”’ +@ProcName+”’) AND type in (N”P”, N”PC”)) DROP PROCEDURE ‘+@ProcName
    INSERT INTO @StoredProcsList
    SELECT @PExist
    FETCH NEXT
    FROM @SPCHECKEXIST INTO @ProcName
    END
    CLOSE @SPCHECKEXIST
    DEALLOCATE @SPCHECKEXIST

    DECLARE @FUNCHECKEXIST CURSOR
    SET @FUNCHECKEXIST = CURSOR FOR
    –Select
    SELECT name FROM sys.objects where type_desc in (‘SQL_SCALAR_FUNCTION’,’SQL_TABLE_VALUED_FUNCTION’)
    OPEN @FUNCHECKEXIST
    FETCH NEXT
    FROM @FUNCHECKEXIST INTO @FunName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @PExist= ‘IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N”’ +@FunName+”’) AND type in (N”FN”, N”IF”, N”TF”, N”FS”, N”FT”)) DROP FUNCTION ‘+@FunName
    INSERT INTO @StoredProcsList
    SELECT @PExist
    FETCH NEXT
    FROM @FUNCHECKEXIST INTO @FunName
    END
    CLOSE @FUNCHECKEXIST
    DEALLOCATE @FUNCHECKEXIST

    DECLARE @SPGENERATESCRIPT CURSOR
    SET @SPGENERATESCRIPT = CURSOR FOR
    –Select
    SELECT name FROM sys.objects WHERE type = ‘P’
    OPEN @SPGENERATESCRIPT
    FETCH NEXT
    FROM @SPGENERATESCRIPT INTO @ProcName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    INSERT INTO @StoredProcsList
    SELECT @setval1
    INSERT INTO @StoredProcsList
    SELECT @setval2
    INSERT INTO @StoredProcsList
    SELECT @setval3
    INSERT INTO @StoredProcsList
    SELECT @setval2
    INSERT INTO @StoredProcsList
    EXEC sp_helptext @ProcName
    FETCH NEXT
    FROM @SPGENERATESCRIPT INTO @ProcName
    END
    CLOSE @SPGENERATESCRIPT
    DEALLOCATE @SPGENERATESCRIPT

    DECLARE @FUNGENERATESCRIPT CURSOR
    SET @FUNGENERATESCRIPT = CURSOR FOR
    –Select
    SELECT name FROM sys.objects where type_desc in (‘SQL_SCALAR_FUNCTION’,’SQL_TABLE_VALUED_FUNCTION’)
    OPEN @FUNGENERATESCRIPT
    FETCH NEXT
    FROM @FUNGENERATESCRIPT INTO @FunName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    INSERT INTO @StoredProcsList
    SELECT @setval1
    INSERT INTO @StoredProcsList
    SELECT @setval2
    INSERT INTO @StoredProcsList
    SELECT @setval3
    INSERT INTO @StoredProcsList
    SELECT @setval2
    INSERT INTO @StoredProcsList
    EXEC sp_helptext @FunName
    INSERT INTO @StoredProcsList
    SELECT @setval2
    FETCH NEXT
    FROM @FUNGENERATESCRIPT INTO @FunName
    END
    CLOSE @FUNGENERATESCRIPT
    DEALLOCATE @FUNGENERATESCRIPT

    SELECT name FROM @StoredProcsList

    END
    ELSE IF @getValue = ‘D_USER’
    BEGIN
    select princ.name
    , princ.type_desc
    , perm.permission_name
    , perm.state_desc
    , perm.class_desc
    , object_name(perm.major_id)
    from sys.database_principals princ
    left join
    sys.database_permissions perm
    on perm.grantee_principal_id = princ.principal_id
    where type_desc in (‘SQL_USER’) and state_desc in (‘GRANT’)
    END

    GO

  35. Hi, can this process be done through the maintenance plan ? is there some way this can be scheduled automatically. I use sql server 2008 and 2012

  36. I tried the above mentioned thing in my system to move the DB from 2008 R2 to 2005, script has been successfully taken. but the below mentioned error is getting occurred while execute the script through SQL Command,

    HResult 0x57, Level 16, State 1
    SQL Network Interfaces: Connection string is not valid [87].
    Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
    shing a connection to the server. When connecting to SQL Server 2005, this failu
    re may be caused by the fact that under the default settings SQL Server does not
    allow remote connections..
    Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

    Kindly have a look into this and let me know the possible result please…

  37. Hi, I want sql query to generate this script.
    I dont want to generate it manualy from ssms.
    Can anybody help me?
    Thanks in advance….

  38. Pingback: SQL SERVER – Weekly Series – Memory Lane – #028 | SQL Server Journey with SQL Authority

  39. Hi, I want to restore a database from sqlserver 2005 to sqlite. Can you explain how to do that? Thanks in Advance.

  40. Hi, I am generating a script for function, once i done and see the location, all my functions are created with suffix of ‘.UserDefinedFunction’. I want to avoid the suffix of ‘.UserDefinedFunction’ while generating script

  41. Hi,

    I am facing one problem while trying to generate Database script of one database,

    i have tried different servers with 4 core & high configurations, its simple database & i am just trying to generate script of Procedure,Functions & Triggers not including tables & data, still its taking too long (approx more than 2 hours) to generate script.

    Enviornment : SQL Server 2008 R2

    * This problem is for specific one database only, in all other database its working good.

    Can anyone please help me ?

  42. it doesnt script the Triggers (table level) nor Stored Procedures. nor Functions.
    have you explored the options at individual object level ??

  43. Hi, I am using sql server 2008 with 2 types of db. But in one db can’t generate scripts.it is showing error like “Index is out of the boundary”. but i tried another db. it is generating scripts good. what is that exact issue? do u know?

  44. After creating the script if i run that script on another computer then it is giving me error like can not find mdf file. do this script does not create new database into the specified path

  45. Sir, Please tell me after creation script file .sql format then how to restore again if script file so large.

  46. Hi! I have a query and it is as follows:

    I wish to create the table structures in excel and create tables in SQL Server 2008 R2. Could you please guide ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s