views:

59

answers:

2

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')
+1  A: 

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

KM
A: 

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

devio