May 4, 2010 at 8:04 am
Hi, I need to form relationships from several-hundred tables to one table. Is there a quick way I can do this (SQL script, etc)?
Thanks
May 4, 2010 at 9:17 am
Are the tables all similar or do they have the same key structure? If so then a script could be generated to do it. But if we are talking about a bunch of different key structures then it is much more difficult.
CEWII
May 4, 2010 at 9:21 am
i remember a post from Jeff Moden mentioning that a view could only have ~200 references; it was less than 255 but more than 200; so if you have 400 tables to join, you might not be able to do ALL of them in a view.
not sure if the same rule applies to a SQL statement, but i would not be surprised.
Lowell
May 4, 2010 at 9:32 am
Lowell,
Perhaps I read that a little differently than you did, I thought he was talking about foreign keys, if he was talking about views then my advice doesn't apply..
CEWII
May 4, 2010 at 9:32 am
All tables have the same key structure.
I am talking about foreign keys into the parent table.
May 4, 2010 at 9:55 am
ok the error you can get is:
“The Maximum number of tables in a query (260) was exceeded”.
i poked around using my Google-Fu, and that seems to be the limit; I saw some speculation that it was increased to 1024, but no citation was given.
this guy seems to be confident in his answer:
http://database.ittoolbox.com/groups/technical-functional/sql-server-l/how-many-joins-1851431
Lowell
May 4, 2010 at 10:11 am
If I understand your requirement this will probably generate a script that can be run to create the relationships you are wanting:
SELECT '--Cmd' = 'ALTER TABLE dbo.'
+ t.name
+ ' ADD CONSTRAINT FK_KeyTableName_'
+ t.name
+ ' FOREIGN KEY ( NonKeyTablePKeyField1, NonKeyTablePKeyField2 ) '
+ 'REFERENCES dbo.KeyTableName ( KeyTablePKeyField1, KeyTablePKeyField2 )'
FROM sys.tables t
WHERE t.name LIKE 'somecriteria%'
You just need to tweak up the where clause to limit it to your list of tables you want. And you need to tweak the field list and key table name..
CEWII
May 4, 2010 at 11:46 am
I understand that you have hundreds of tables, and you want to "form relationships" between them. Could you describe in more detail what the objective is?
I'm thinking that this is perhaps a new database that you've inherited, and you want a script that will query sys.columns and sys.foreign_keys to identify relationships between the tables, perhaps by matching the objects on declared foreign key relationships or common column names?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 10, 2010 at 2:33 am
Hi,
How do I use that query?
To the last poster - I haven't inherited a database, I just have too many tables that it will take too long to join up the relationships manually.
May 10, 2010 at 7:52 am
A long time ago, I was asked to write some custom reporting SQL queries against a 3rd party database containing 100+ tables. It had one unique key on most tables, but some of the smaller reference tables had no constraints or indexing at all. There were no primary or foreign key constaints, because they were enforcing data integrity programatically in the middle tier (at least that was how it was explained to me by one of their implementation people). There was no data dictionary or entity relationship diagram available either.
So, I wrote this SQL script which queries all columns that are members of a primary key or unique index, and it joins each of these columns with all tables that also contain a column of the same name. The objective is to identify logical relationships between tables when declared foreign keys don't exist. Maybe this is sort of what you're looking for.
/*
Query all tables that contain a column contained in another table's primary key or unique index.
*/
select
keys.type_desc as 'constraint_type',
keys.name as 'constraint_name',
schm.name+'.'+tbl.name as 'primary_key_table_name',
cols.name as 'key_column_name',
'-->' as '-->',
xschm.name+'.'+xtbl.name as 'foreign_key_table_name'
from sys.key_constraints as keys
join sys.tables as tbl
on tbl.object_id = keys.parent_object_id
join sys.schemas as schm
on schm.schema_id = tbl.schema_id
join sys.index_columns as idxcols
on idxcols.object_id = tbl.object_id and
idxcols.index_id = keys.unique_index_id
join sys.columns as cols
on cols.object_id = tbl.object_id and
cols.column_id = idxcols.column_id
left join sys.columns xcols
on xcols.name = cols.name
left join sys.tables xtbl
on xtbl.object_id = xcols.object_id
left join sys.schemas as xschm
on xschm.schema_id = xtbl.schema_id
where
tbl.is_ms_shipped = 0 and
schm.name+'.'+tbl.name <> xschm.name+'.'+ xtbl.name and
xtbl.name is not null
order by
schm.name,
tbl.name,
cols.name,
xschm.name,
xtbl.name;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 10, 2010 at 8:01 am
Lowell (5/4/2010)
i remember a post from Jeff Moden mentioning that a view could only have ~200 references; it was less than 255 but more than 200; so if you have 400 tables to join, you might not be able to do ALL of them in a view.not sure if the same rule applies to a SQL statement, but i would not be surprised.
That was for a view with UNION ALL. I suspect this is different.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 10, 2010 at 8:07 am
eric_russell 13013 (5/4/2010)
I understand that you have hundreds of tables, and you want to "form relationships" between them. Could you describe in more detail what the objective is?I'm thinking that this is perhaps a new database that you've inherited, and you want a script that will query sys.columns and sys.foreign_keys to identify relationships between the tables, perhaps by matching the objects on declared foreign key relationships or common column names?
You run the script and take its output and run that against the database and it creates the foreign keys.
CEWII
May 10, 2010 at 8:54 am
Let's assume that there are several (or a hundred) tables which partition client data, and they are all prefixed with the name 'client_data_%', and you need to create on each table a foreign key on the [clientid] column which relates it back to the [client] table. The following query will transform a list of tables into 'ALTER TABLE...' statements, and the result can then be executed as a script.
select 'alter table ' + t.name + ' add constraint fk_' + t.name + '_clientid foreign key (clientid) references Client (clientid);'
from sys.tables t
where name like 'client_data_%';
For example:
alter table client_data_1 add constraint fk_client_clientid foreign key (clientid) references Client (clientid);
alter table client_data_2 add constraint fk_client_clientid foreign key (clientid) references Client (clientid);
alter table client_data_3 add constraint fk_client_clientid foreign key (clientid) references Client (clientid);
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 10, 2010 at 2:34 pm
info-849042 (5/4/2010)
Hi, I need to form relationships from several-hundred tables to one table. Is there a quick way I can do this (SQL script, etc)?
Everything and anything can be done with a script.
Problem here is that I'm not sure yet what you mean with "form relationships from several-hundred tables to one table"... are we talking referential integrity?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply