November 13, 2006 at 1:17 pm
Hi.
I am trying to create a query that reads out tables that a list of stored procedures are depended on, and I have had success by selecting from the sys.sql_dependencies.
I can select the DDL of the stored procedures from sys.syscomments, but I cannot find anyway to select the original DDL statements for the Tables.
Please help.
November 13, 2006 at 2:29 pm
Are these all regular tables or are you talking about the temp tables you create in the procs themselves?
November 13, 2006 at 3:23 pm
These are all regular tables.
November 14, 2006 at 5:20 am
Well I don;t know that you can grab the exact DDL from the systables but this will give you some of the info
SELECT syscolumns.name AS ColumnName, systypes.name AS Datatype, syscolumns.length as characterlgth
FROM syscolumns, systypes,sysobjects
WHERE sysobjects.id = syscolumns.id AND
syscolumns.xtype = systypes.xtype and
sysobjects.name = 'tablename'
if its not a lot of tables, and you're using SQL2k5 Management studio you can right click and script out the table creation script.. if there are a lot of tables in a single DB you can script all objects in the DB and copy out the tables you need.. hacky but it works...
November 15, 2006 at 11:24 am
Yeah, I don't think you can actually get at the DDL for tables because they're not "text objects", unlike SPs, functions etc. You can (shameless plug) use a tool like the Red Gate SQL Compare command line or toolkit API (both available as part of the SQL Toolkit) to script out the tables you're interested in to a file if the SQL Server Management Studio approach won't work for you (for example, if you have lots of tables, or you need to automate the process on a regular basis).
URL for the Red Gate tools is: http://www.red-gate.com/products/SQL_Toolkit/index.htm.
HTH,
Bart
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply