generate schema from stored procedures

Is there a way to make a schema 开发者_如何学JAVAdiagram from an SQL Server database using the stored procedures of this database?

I don't mind if I must use an external software.

You could try playing around with CodeSmith Generator. It's SchemaExplorer Schema Discovery API allows you to programmatically access database elements for a given database and do something creative with it. However, it will still be logically hard to reverse-engineer a schema/diagram this way.

You can build a SQLCLR procedure which uses the Scripter Class from the SMO library.

update: more info on the question reveals the idea is to generate a table schema with dependencies based on the content of the stored procedures.

The approach would be to generate the table structure from the information_schema views and then parse the contents of the syscomments table to figure out the relations. This will always be approximate as it is very hard to establish the one-to-many relationships purely from the SQL Statements. I think you can make a guess based on the field which is referenced more.

If you can't see the tables then you can not generate the schema.

That is, you can't if you have permissions on stored procedures only.

At least two reasons:

• the stored proc may JOIN and use several tables
• you can't see constraints, indexes, keys etc even if you had table names

Basically, you can only:

• see what you have permissions on in SSMS etc
• see the internals if you have VEIW DEFINITION rights

Edit, after clarification

There is no way to script implied aspects (such as missing foreign keys) of the schema from code