Equivalent to Generate Scripts in TSQL?

  • Is there an equivalent to the Tasks -> generate Scripts - Schema and Data tool in ssms that can be run from TSQL or powershell?  The primary reason I am looking to do this is to copy a compete database Without having to make a backup.  I have permissions on the production Server that are sufficient to generate the creation scripts for schema and data, but do not have permissions to create backups or use the copy database tool.

  • In TSQL, there is a function, OBJECT_DEFINITION().  It doesn't work with all object types however.

    There is a SCRIPTER method in SQL Server plugin for Powershell.

  • Beating my own drum here, but i put together a procedure that queries the metadata to script tables and objects:
    I try to maintain it as best I can, it's been around for a while.
    https://www.sqlservercentral.com/Forums/FindPost1913801.aspx

    there are a few things it does not script like temporal tables, im momort tables,partitioned tables,and exotic stuff like that, but it certainly does a good job.

    the article is a bit dated, but the direct links to the latest code are up to date.
    here's how i do exactly what you are asking:

    CREATE TABLE #MyObjectHierarchy
     (
      HID int identity(1,1) not null primary key,
      ObjectId int,
      TYPE int,OBJECTTYPE AS CASE
              WHEN TYPE = 1 THEN 'FUNCTION'
              WHEN TYPE = 4 THEN 'VIEW'
              WHEN TYPE = 8 THEN 'TABLE'
              WHEN TYPE = 16 THEN 'PROCEDURE'
              WHEN TYPE =128 THEN 'RULE'
              ELSE ''
             END,
     ONAME varchar(255),
     OOWNER varchar(255),
     SEQ int
     )

    --our results table
    CREATE TABLE #Results(ResultsID int identity(1,1) not null,ResultsText varchar(max) )
    --our list of objects in dependancy order
    INSERT #MyObjectHierarchy (TYPE,ONAME,OOWNER,SEQ)
      EXEC sp_msdependencies @intrans = 1

    Update #MyObjectHierarchy SET ObjectId = object_id(OOWNER + '.' + ONAME)
    --synonyns are object type 1 Function?!?!...gotta remove them
    DELETE FROM #MyObjectHierarchy WHERE objectid in(
      SELECT [object_id] FROM sys.synonyms UNION ALL
      SELECT [object_id] FROM master.sys.synonyms)
    DECLARE
      @schemaname  varchar(255),
      @objname   varchar(255),
      @objecttype  varchar(20),
      @FullObjectName varchar(510)

    DECLARE cur1 CURSOR FOR
      SELECT OOWNER,ONAME,OBJECTTYPE FROM #MyObjectHierarchy ORDER BY HID
    OPEN cur1
    FETCH NEXT FROM cur1 INTO @schemaname,@objname,@objecttype
    WHILE @@fetch_status <> -1
    BEGIN
       SET @FullObjectName = @schemaname + '.' + @objname
       IF @objecttype = 'TABLE'
       BEGIN
        INSERT INTO #Results(ResultsText)
         EXEC sp_getddl @FullObjectName
       END
       ELSE IF @objecttype IN('VIEW','FUNCTION','PROCEDURE')--it's a FUNCTION/PROC/VIEW
       BEGIN
        --CREATE PROC/FUN/VIEW object needs a GO statement
        INSERT INTO #Results(ResultsText)
         SELECT 'GO'
        INSERT INTO #Results(ResultsText)
         EXEC sp_helptext @FullObjectName
       END
     
      FETCH NEXT FROM cur1 INTO @schemaname,@objname,@objecttype
     END
      CLOSE cur1
      DEALLOCATE cur1
    SELECT ResultsText FROM #Results ORDER BY ResultsID

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Steven.Grzybowski - Monday, August 6, 2018 9:35 AM

    Is there an equivalent to the Tasks -> generate Scripts - Schema and Data tool in ssms that can be run from TSQL or powershell?  The primary reason I am looking to do this is to copy a compete database Without having to make a backup.  I have permissions on the production Server that are sufficient to generate the creation scripts for schema and data, but do not have permissions to create backups or use the copy database tool.

    There's usually a reason to limit the permissions. If you keep trying to find alternatives, you'll only get more options unavailable. Request a proper backup to the DBA in charge.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

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

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