Script the table structure ( create table or alter table) for all the tables in the database

• BoobyB

  SSCertifiable

  Points: 5470

  More actions

  March 7, 2017 at 8:47 am

  #335528

  Hi All,

  I am looking for a way to script out all the tables. views and SP's in a database but accomplish it through script. I know how to do it through  GUI ( generate script option) but need it in a script. Does any one has a script that does that ?

  Thanks in advance.

  B

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  March 7, 2017 at 8:54 am

  #1932066

  Have you tried this?

  John

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  March 7, 2017 at 11:19 am

  #1932100

  This might help: https://www.sqlservercentral.com/Forums/Topic751783-566-1.aspx

  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

• Lowell

  SSC Guru

  Points: 323522

  More actions

  March 7, 2017 at 12:21 pm

  #1932127

  There is nothing built in to SQL to do it via script; Even SSMS uses SMO to script objects out, it's not TSQL native.
  Luis was kind enough to link to the procedure I maintain for this specific purpose; the code now assumes SQL 2008 and above(as it now assumes columns with filtered indexes exist.
  I keep tweaking it and updating it when the need arises.

  to script everything, you'll have to loop through all the objects in a  cursor;
  I've done it with the procedure below, which has worked fine for me so far.

  IF OBJECT_ID('[dbo].[sp_export_all]') IS NOT NULL
DROP PROCEDURE [dbo].[sp_export_all]
GO
--#################################################################################################
--developer utility function added by Lowell, used in SQL Server Management Studio
--Purpose: Script All Tables/Procs/views/functions/objects, potentially with data
--#################################################################################################
CREATE PROCEDURE sp_export_all(@WithData int = 0)
AS
BEGIN
SET NOCOUNT ON
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
         IF @WithData > 0
     INSERT INTO #Results(ResultsText)
     EXEC sp_export_data @table_name = @FullObjectName,@ommit_images = 1
    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
END

  GO
--#################################################################################################
--Mark as a system object
EXECUTE sp_ms_marksystemobject '[dbo].[sp_export_all]'
--#################################################################################################


  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!

• BoobyB

  SSCertifiable

  Points: 5470

  More actions

  March 8, 2017 at 6:21 am

  #1932273

  Thank you very much, this is exactly what I was looking for.

  Many Thanks,
  B