EDIT - On closing & reopening MSSQL the changes have taken effect fully. I can handle the 4 FK constraints manually; that's no big deal but would appreciate if someone can help in doing this for PK's?
After importing to MSSQL I want to convert certain FK's/ PK's data-types. So need to drop all FK constraints, then PK constraints in db where different schemas exist, to allow editing data-types of the PK/FK's then I'll recreate the relationships manually again. New to MSSQL so advanced Stored Procedures & dynamic SQL so bit much atm.
Few threads out there relating to FK's, Below (post-3 [answered Oct 4, 2012 at 7:45 by Yaroslav]) executes.
Originally had 56 relationships, now showing 4 relationships when I run below script. Yet if I go into a Table Design right-click field > Relationships the existing relationships (which are not listed in the below 4 outstanding) exist. Despite refreshing the entire db in Object Explorer. Generating a new Diagram also show the relationships still existing.
If we're able to get the above working be most grateful if someone could alter code to do PK's also. Hopefully once I've drafted a few Stored Procedures... spent a bit more time in MSSQL it'll become apparent.
After importing to MSSQL I want to convert certain FK's/ PK's data-types. So need to drop all FK constraints, then PK constraints in db where different schemas exist, to allow editing data-types of the PK/FK's then I'll recreate the relationships manually again. New to MSSQL so advanced Stored Procedures & dynamic SQL so bit much atm.
Few threads out there relating to FK's, Below (post-3 [answered Oct 4, 2012 at 7:45 by Yaroslav]) executes.
Code:
DECLARE @SQL VARCHAR(MAX)=''
SELECT @SQL = @SQL + 'ALTER TABLE ' + QUOTENAME(FK.TABLE_SCHEMA) + '.' + QUOTENAME(FK.TABLE_NAME) + ' DROP CONSTRAINT [' + RTRIM(C.CONSTRAINT_NAME) +'];' + CHAR(13)
--SELECT K_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
EXEC (@SQL)
PRINT @SQLOriginally had 56 relationships, now showing 4 relationships when I run below script. Yet if I go into a Table Design right-click field > Relationships the existing relationships (which are not listed in the below 4 outstanding) exist. Despite refreshing the entire db in Object Explorer. Generating a new Diagram also show the relationships still existing.
Code:
SELECT *
FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
WHERE CONSTRAINT_NAME IN ( SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' )
SELECT *
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE CONSTRAINT_NAME IN ( SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' )
SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'If we're able to get the above working be most grateful if someone could alter code to do PK's also. Hopefully once I've drafted a few Stored Procedures... spent a bit more time in MSSQL it'll become apparent.
Last edited:
) I have around 108,000 records * 6 average sub-records = 648,000 sub-records in this one area alone. relating to one container. I won't go into detail spilling out my application. On my very first calculation which is on the very first small item going from an Int to TintyInt is a 1.944 MB saving. Viewed thousands of times throughout this relevant grandparent container. Then compound that with circa half a dozen/ a dozen e.g's like this which are more nested than this example & several users accessing remotely. If I'm wrong I'm wrong & that's fine, appreciate I'm not considering key-compression/ caching I think it's quite a complex schema/ project; abnormal to most normalization rules; where historical records are necessary & not considered redundant data.
