CODEWITHSUNDEEP
sqlsql-server
Panagiotis Lefas
Panagiotis Lefas

Reputation: 1155

generate schema from stored procedures

Is there a way to make a schema diagram from an SQL Server database using the stored procedures of this database?

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

Upvotes: 3

Views: 1762

Answers (3)

Jakob Möllås
Jakob Möllås

Reputation: 4369

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.

Upvotes: 1

gbn
gbn

Reputation: 432200

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

Upvotes: 0

Filip De Vos
Filip De Vos

Reputation: 11908

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.

Upvotes: 0

Related Questions