SqlServer: How to get meta-data about tables and their relationships?

Question

I was wondering if there was a way (relatively simple I hope) to get information about the table and its attributes and realtionships?

Clarification: I want to grab all tables in the database and get the meta-model for the whole database, tables, column data, indicies, unique constraints, relationships between tables etc.

score 3 · Answer 1 · edited May 23 '17 at 12:13

3

The system has a data dictionary in sys.tables, sys.columns, sys.indexes and various other tables. You can query these tables to get metadata about the database structure. This posting has a script I wrote a few years ago to reverse engineer a database schema. If you take a look at it you can see some examples of how to use the system data dictionary tables.

edited May 23 '17 at 12:13

Community

1
1

answered Apr 06 '11 at 15:10

ConcernedOfTunbridgeWells

64,444
15
143
197

DForck42 · Answer 2 · 2011-04-06T19:30:06.877

2

there are a whole bunch of system views in the information_schema schema in sql server 2005+. is there anything in particular you're wanting?

some of those views include: check_contraints, columns, tables, views

edited Apr 06 '11 at 19:30

answered Apr 06 '11 at 15:01

DForck42

19,789
13
59
84

score 1 · Answer 3 · answered Apr 06 '11 at 15:01

1

Try sp_help <tablename>. This will show you foreign key refrences and data about the columns, etc - that is, if you are interested in a specific table, as your question seemed to indicate.

answered Apr 06 '11 at 15:01

Quantum Elf

752
4
9

For all tables you could also do `EXEC sp_msforeachtable 'sp_help ?'` – JNK Apr 06 '11 at 15:57
@JNK - love your Gravatar - if any questions come up on SO about diagnosing faults with AE-35 units I would also recommend your expertise there. :) – Quantum Elf Apr 06 '11 at 19:41

score 0 · Answer 4 · answered Apr 17 '14 at 08:56

0

You can try to use this library db-meta

answered Apr 17 '14 at 08:56

tomcat

45
1
1
3

score 0 · Answer 5 · answered Mar 13 '12 at 22:54

If using .NET code is an option SMO is the best way to do it.

It abstracts away all these system views and tables hiding them behind nice and easy to use classes and collections. http://msdn.microsoft.com/en-us/library/ms162169.aspx

This is the same infrastructure SQL Server Management Studio uses itself. It even supports scripting.

Abstraction comes at a cost though so you need maximum performance you'd still have to use system views and custom SQL.

SqlServer: How to get meta-data about tables and their relationships?

5 Answers5

Linked