Script all objects in a database into a table

Question

I need to populate a table in SQL server 2005 with some objects in a particular database (stored procedures, tables, views and functions, etc)

In the table I would like one line per object and the table columns will be the name of the object , the script of the object, schema and object type.

I have tried to use the script below but this does not return script for tables. Is there a way to return all objects with its scripts?

    select a.name, * 
from 
    Enterprise..sysobjects a 
    inner join Enterprise..syscomments b on a.id=b.id 
where
    a.xtype in ('FN','IF','P','TF','TR','U','V','X')

All of that data will already be in the database in the systems tables. What do you need to do this for? — Russ Cam
– Russ Cam, Commented Sep 30, 2009 at 15:09
Basically I want store the create-table script for the tables in my own table. Similar to the create-procedure scipts that i copied to my own tables that was held in the sysobjects table. We will be using this to track changes that are made in the database. — MarcoF
– MarcoF, Commented Sep 30, 2009 at 15:28

Community · Accepted Answer · 2017-05-23 12:30:28Z

1

scripting tables is difficult, look here: In MSSQL, how do I generate a CREATE TABLE statement for a given table?

you best bet might be to make a CLR that uses SMO to generate the scripts, see:Scripting Database Objects using SMO

If you just want to track the database changes, possibly a DDL trigger to log changes, see: Using DDL Triggers to Manage SQL Server 2005

edited May 23, 2017 at 12:30

CommunityBot

11 silver badge

answered Sep 30, 2009 at 15:43

KM.

104k34 gold badges183 silver badges214 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

devio · Accepted Answer · 2009-10-28 06:55:21Z

0

If you want to have a database (plus web app) which tracks changes in other databases, dbscript might help. (disclaimer: I wrote it ;))

answered Oct 28, 2009 at 6:55

devio

37.3k7 gold badges85 silver badges147 bronze badges

Collectives™ on Stack Overflow

Script all objects in a database into a table

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related