[Feature request MSSQL]

[expired user #8813]'s profile image [expired user #8813] posted 9 years ago in General Permalink
Hi,
I can't see foreign keys of my tables through user interface when clicking on table, also CREATE code isn't correct. MS SQL 2008 RC2. Can you please fix it?
sorry for my english :P


CREATE TABLE "Questions" (
"ID" INT NOT NULL PRIMARY KEY IDENTITY,
"ID_Topic" INT NOT NULL,
"Type" INT NOT NULL,
"Name" NCHAR(255) NOT NULL,
"Text" NTEXT NOT NULL,
"FreeAnswer" BIT NOT NULL DEFAULT '0'
);
CREATE TABLE "Multimedia" (
"ID_Question" INT NOT NULL,	
"Multimedia" VARBINARY NOT NULL,
);
alter table Multimedia ADD FOREIGN KEY (ID_Question) REFERENCES Questions(id) ON DELETE CASCADE;
ansgar's profile image ansgar posted 9 years ago Permalink
If you post some example query for how to detect foreign keys from a table, I can see if I can add that to the table designer.
[expired user #8813]'s profile image [expired user #8813] posted 9 years ago Permalink
easy:
select * from information_schema.table_constraints where TABLE_NAME='Multimedia' and CONSTRAINT_TYPE='FOREIGN KEY'
[expired user #8813]'s profile image [expired user #8813] posted 9 years ago Permalink
this will give you all information

SELECT C.TABLE_CATALOG [PKTABLE_QUALIFIER], 
C.TABLE_SCHEMA [PKTABLE_OWNER], 
C.TABLE_NAME [PKTABLE_NAME], 
KCU.COLUMN_NAME [PKCOLUMN_NAME], 
C2.TABLE_CATALOG [FKTABLE_QUALIFIER], 
C2.TABLE_SCHEMA [FKTABLE_OWNER], 
C2.TABLE_NAME [FKTABLE_NAME], 
KCU2.COLUMN_NAME [FKCOLUMN_NAME], 
RC.UPDATE_RULE, 
RC.DELETE_RULE, 
C.CONSTRAINT_NAME [FK_NAME], 
C2.CONSTRAINT_NAME [PK_NAME], 
CAST(7 AS SMALLINT) [DEFERRABILITY] 
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS C 
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU 
ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA 
AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME 
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC 
ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2 
ON RC.UNIQUE_CONSTRAINT_SCHEMA = C2.CONSTRAINT_SCHEMA 
AND RC.UNIQUE_CONSTRAINT_NAME = C2.CONSTRAINT_NAME 
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 
ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA 
AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME 
AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION 
WHERE  C.CONSTRAINT_TYPE = 'FOREIGN KEY'


http://www.mssqltips.com/sqlservertip/1151/identify-all-of-your-foreign-keys-in-a-sql-server-database/

Please login to leave a reply, or register at first.