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, Sep 30 '09 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, Sep 30 '09 at 15:28

score 1 · Accepted Answer · edited May 23 '17 at 12:30

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 '17 at 12:30

Community

1
1

answered Sep 30 '09 at 15:43

KM.

101,727
34
178
212

score 0 · Answer 2 · answered Oct 28 '09 at 06:55

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 '09 at 06:55

devio

36,858
7
80
143

Script all objects in a database into a table

2 Answers2