Create database model using columns names (no FK, no relation)

Question

I have a database with no FKs, PKs and not any documentation to show what tables relates with each other. So i'm in big trouble to do a reverse engineering on the physical data to mind how does one table relates to another one. Does anyone knows a tool to create a model based only by the names of columns?

Example: Table A have a column with name ID_NTP_BAZINGA that relates to table B with the column name ID_NTP_BAZINGA.

Answer 1

Hm, it is hard task to do (and I kind of have done it myself), but I can think of some hints to automate your work, for example commands like sp_msforeachdb or sp_msforeachtable might be handy.

To determine FK relations in a way you mentioned, you could use such query:

select * from (
    select object_name(object_id) [table],
           name,
           count(*) over (partition by name) [cnt]
    from [DB_name].sys.columns
) a where cnt > 1

which, in your case, would return (among others)

Table A | ID_NTP_BAZINGA
Table B | ID_NTP_BAZINGA

and give you already some insight.

For candidates for PK, one could use sp_msforeachtable with dynamic SQL checking if count(distinct *) is equal to count(*) - this would tell you whether you have unique values or not, and in the end you would count(*) with is not null filter in where clause, so you'll know if you have nulls in particular column.

These are some general hints and exact queries you have to write yourself. This answer will get you started.