There are bulk operation like things I cannot do from the interface. like transferring hundredths of blog related pages when I totally rebuild the database and it's contents by another application etc. It would really help If I can find out the table relationships from within the database so that I can figure out the order of things need to be done and what needs to be done. These relationships do not have to be enforced for this purpose (in sql server we create a relationship with nocheck clause, it's just there for definition but it does not cause any problems in the application like you can delete the parent if the child exists since the sql server will not enforce this key, one thing though you cannot use truncate command to the parent with any kind of foreign key on it, disabled or not). I created a script suggesting which columns might be relational (I matched the ending of columnnames to the parent, like ID primary key in the parent will match to ParentId, in this case is a bad match but these are suggestions anyway, it also prints out a foreign key script(2 columns one with checked foreign key one with nocheck foreign key) so that maybe you can paste the results to excel choose the ones you think are valid and apply to the next release of if you can't maybe send that script to me I'll save it here and even though you upgrade the database later I keep using that script. That will make my modifications to the database a lot easier. Anything I do to the database , scripts etc if you are interested I can send it to you, I am not a UI guy but I am used to database integrations etc, bulk data related stuff, been doing that for a while.

If you can play with this script little and let me know what you think of it that would be nice.

Thank you

;
WITH cols
AS (SELECT s.name AS SchemaName,
o.name AS TableName,
c.name AS ColumnName,
t.name AS TypeName,
o.OBJECT_ID,
c.column_id,
i.key_ordinal,
max_key_ordinal
FROM sys.tables o
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
INNER JOIN sys.columns c ON c.object_id = o.object_id
INNER JOIN sys.types t ON t.user_type_id = c.user_type_id
OUTER APPLY (SELECT ic.key_ordinal,
(SELECT MAX(k.key_ordinal)
FROM sys.index_columns k
WHERE k.OBJECT_ID = o.OBJECT_ID
AND k.index_id = i.index_id) AS max_key_ordinal
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id
WHERE i.is_primary_key = 1
AND i.object_id = o.object_id
AND ic.object_id = o.object_id
AND ic.column_id = c.column_id) i
WHERE o.is_ms_shipped = 0),
fklist
AS (SELECT fk.name AS ForeignKeyName,
ced.SchemaName AS RefdSchemaName,
ced.TableName AS RefdTableName,
ced.TypeName,
ced.ColumnName AS RefdColumnName,
cng.SchemaName AS RingSchemaName,
cng.TableName AS RingTableName,
cng.ColumnName AS RingColumnName,
fc.constraint_column_id
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fc ON fk.object_id = fc.constraint_object_id
INNER JOIN cols ced ON ced.OBJECT_ID = fk.referenced_object_id
AND ced.column_id = fc.referenced_column_id
INNER JOIN cols cng ON cng.OBJECT_ID = fk.parent_object_id
AND cng.column_id = fc.parent_column_id),
possiblefklist
AS (SELECT a.SchemaName AS RefdSchemaName,
a.TableName AS RefdTableName,
a.ColumnName AS RefdColumnName,
b.SchemaName AS RingSchemaName,
b.TableName AS RingTableName,
b.ColumnName AS RingColumnName,
a.TypeName
FROM (SELECT * FROM cols WHERE max_key_ordinal = 1 AND key_ordinal = 1) AS a
INNER JOIN (SELECT * FROM cols WHERE key_ordinal IS NULL) b ON a.ColumnName = RIGHT(b.ColumnName, LEN(a.ColumnName))
AND a.TypeName = b.TypeName)
SELECT b.ForeignKeyName,
a.*,
CASE WHEN b.ForeignKeyName IS NULL
THEN 'ALTER TABLE [' + a.RingSchemaName + '].[' + a.RingTableName + '] ADD CONSTRAINT [FK_' + REPLACE(a.RingTableName, 'mp_', '') + '_'
+ REPLACE(a.RefdTableName, 'mp_', '') + '] FOREIGN KEY ([' + a.RingColumnName + '_' + a.RingColumnName + ']) REFERENCES ['
+ a.RefdSchemaName + '].[' + a.RefdTableName + '] ([' + a.RefdColumnName + ']) ON DELETE CASCADE
GO'
END AS FKScript,
CASE WHEN b.ForeignKeyName IS NULL
THEN 'ALTER TABLE [' + a.RingSchemaName + '].[' + a.RingTableName + '] WITH NOCHECK ADD CONSTRAINT [FK_' + REPLACE(a.RingTableName, 'mp_', '')
+ '_' + REPLACE(a.RefdTableName, 'mp_', '') + '_' + a.RingColumnName + '] FOREIGN KEY ([' + a.RingColumnName + ']) REFERENCES ['
+ a.RefdSchemaName + '].[' + a.RefdTableName + '] ([' + a.RefdColumnName + '])
GO
ALTER TABLE [' + a.RingSchemaName + '].[' + a.RingTableName + '] NOCHECK CONSTRAINT [FK_' + REPLACE(a.RingTableName, 'mp_', '') + '_' + REPLACE(a.RefdTableName,
'mp_', '') + ']
GO'
END AS NocheckFKScript
FROM possiblefklist a
LEFT OUTER JOIN fklist b ON a.RefdSchemaName = b.RefdSchemaName
AND a.RefdTableName = b.RefdTableName
AND a.RingSchemaName = b.RingSchemaName
AND a.RingTableName = b.RingTableName
AND a.RingColumnName = b.RingColumnName
AND a.RefdColumnName = b.RefdColumnName
-- you can customize the filter here
WHERE b.ForeignKeyName IS NULL -- Find out possible missing foreign keys
AND (a.RefdTableName LIKE 'mp[_]%'
OR a.RefdTableName LIKE 'ws[_]%')
AND (a.RingTableName LIKE 'mp[_]%'
OR a.RingTableName LIKE 'ws[_]%')
AND a.TypeName NOT IN ('uniqueidentifier')