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?


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

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
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.

| improve this answer | |

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

| improve this answer | |
  • adding onto this in spirit, you can also do the same with powershell2.0 – jcolebrand Nov 12 '11 at 3:29

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:

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'
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.