Is there a way to programmatically script all objects associated with a given table?

Question

I know that in SQL management studio I can right click a table/trigger/key and script object as.... Is there a way to do this programmatically, given an object's name?

If so, is there a way to find all objects (primary keys, foreign keys, triggers) associated with a given table and script all of them programmatically?

Answer 1

A way to get started with this would be the following:

DECLARE @TableName VARCHAR(50)
DECLARE @ObjectID INT
SET @TableName = '' -- the name of the objects you want to investigate

SELECT @ObjectID = [id]  FROM sysobjects WHERE name=@TableName

SELECT * FROM sysobjects WHERE name=@TableName
UNION
SELECT * FROM sysobjects WHERE id in (SELECT id FROM sysdepends WHERE depid= @ObjectID)

The SysDepends "table" will tell you which objects are dependent upon another. It is hierarchical, so you may have to recursively run through SysDepends until you start getting nulls. Sometimes, sysdepends is incomplete, here is an article with some other suggestions.

The SysObjects "table" will tell you some stuff about the objects in the database. The type (also xtype) columns tell you what the item is: user-defined table, stored proc, trigger, etc.

Then you will want sp_helptext to spit out the text of a stored procedure. This will not reproduce the text of an encrypted stored procedure.

Any full and complete solution will involve programming something, especially when encrypted stored procedures and triggers are involved. One sample article on programatically determining the items in the DB. The datatype needed to decrypt SQL Server 2000 stored procs showed up in SQL Server 2005, so you could not use SQL in SQL Server 2000 to decrypt its own encrypted stored procs (but you could decrypt them in SQL in SQL Server 2005) and it would not surprise me if the same were true for the 2005 to 2008 transition. I lost interest in decrypting stored procedures several years ago.

Answer 2

This is a technique in C# using Server Management Objects - I don't know of a way to do it in pure T-SQL.

Answer 3

I know how to programatically find all of the information that you need to script the objects. But to actually script them, you might have to write the script generation code yourself.

If you're writing code to find out all about the existing objects, the term to google for is "SQL Server Data Dictionary".

I'll give you some starting examples.

To find all foreign key constraints on a specific table:

select * from information_schema.table_constraints where CONSTRAINT_TYPE = 'FOREIGN KEY'
and TABLE_NAME = 'aspnet_Roles'

To find all of the foreign key constraints that reference a specific table:

select 
sys.foreign_keys.name as key_name,
pt.name as parent_table_name,
pc.name as parent_column_name,
ct.name as referenced_table_name,
cc.name as referenced_colum_name 
from sys.foreign_key_columns
inner join sys.foreign_keys on sys.foreign_key_columns.constraint_object_id = sys.foreign_keys.object_id
inner join sys.tables pt on sys.foreign_key_columns.parent_object_id = pt.object_id
inner join sys.tables ct on sys.foreign_key_columns.referenced_object_id = ct.object_id
inner join sys.columns pc on sys.foreign_key_columns.parent_object_id = pc.object_id and sys.foreign_key_columns.parent_column_id = pc.column_id
inner join sys.columns cc on sys.foreign_key_columns.referenced_object_id = cc.object_id and sys.foreign_key_columns.referenced_column_id = cc.Column_id
where ct.name = 'aspnet_Applications'