Usefull Generic Query 6.5 / 7.0

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Usefull Generic Query 6.5 / 7.0

Author

Topic

sneethling
Starting Member

22 Posts

Posted - 2001-03-07 : 03:52:56

/* Run this Query against any SQL 6.5 / 7.0 User DB */
/* It return results for tables less than 8 columns as Primary Key */
/* If you think this is usefull for you please have a look at my "Generic Query Question" -> Please!!! Reply */

SELECT CONVERT(VARCHAR(30),T1.NAME) Parent,
CONVERT(VARCHAR(20),T6.NAME) Parent_Column,
CONVERT(VARCHAR(30),T2.NAME) Child,
convert(varchar(20),T5.NAME) Child_Column,
CONVERT(VARCHAR(30),T3.NAME) Constraint_Name
FROM SYSOBJECTS T1, SYSOBJECTS T2, SYSOBJECTS T3, SYSREFERENCES T4, SYSCOLUMNS T5, SYSCOLUMNS T6
WHERE T1.TYPE = "U"
AND T2.TYPE = "U"
AND T3.TYPE = "F"
AND T1.ID = T4.RKEYID
AND T4.FKEYID = T2.ID
AND T3.ID = T4.CONSTID

AND T5.ID = T2.ID
AND T5.COLID = T4.FKEY1
AND T6.ID = T1.ID
AND T6.COLID = T4.RKEY1
union
SELECT CONVERT(VARCHAR(30),T1.NAME) Parent,
CONVERT(VARCHAR(20),T6.NAME) Parent_Column,
CONVERT(VARCHAR(30),T2.NAME) Child,
convert(varchar(20),T5.NAME) Child_Column,
CONVERT(VARCHAR(30),T3.NAME) Constraint_Name
FROM SYSOBJECTS T1, SYSOBJECTS T2, SYSOBJECTS T3, SYSREFERENCES T4, SYSCOLUMNS T5, SYSCOLUMNS T6
WHERE T1.TYPE = "U"
AND T2.TYPE = "U"
AND T3.TYPE = "F"
AND T1.ID = T4.RKEYID
AND T4.FKEYID = T2.ID
AND T3.ID = T4.CONSTID

AND T5.ID = T2.ID
AND T5.COLID = T4.FKEY2
AND T6.ID = T1.ID
AND T6.COLID = T4.RKEY2
union
SELECT CONVERT(VARCHAR(30),T1.NAME) Parent,
CONVERT(VARCHAR(20),T6.NAME) Parent_Column,
CONVERT(VARCHAR(30),T2.NAME) Child,
convert(varchar(20),T5.NAME) Child_Column,
CONVERT(VARCHAR(30),T3.NAME) Constraint_Name
FROM SYSOBJECTS T1, SYSOBJECTS T2, SYSOBJECTS T3, SYSREFERENCES T4, SYSCOLUMNS T5, SYSCOLUMNS T6
WHERE T1.TYPE = "U"
AND T2.TYPE = "U"
AND T3.TYPE = "F"
AND T1.ID = T4.RKEYID
AND T4.FKEYID = T2.ID
AND T3.ID = T4.CONSTID

AND T5.ID = T2.ID
AND T5.COLID = T4.FKEY3
AND T6.ID = T1.ID
AND T6.COLID = T4.RKEY3
union
SELECT CONVERT(VARCHAR(30),T1.NAME) Parent,
CONVERT(VARCHAR(20),T6.NAME) Parent_Column,
CONVERT(VARCHAR(30),T2.NAME) Child,
convert(varchar(20),T5.NAME) Child_Column,
CONVERT(VARCHAR(30),T3.NAME) Constraint_Name
FROM SYSOBJECTS T1, SYSOBJECTS T2, SYSOBJECTS T3, SYSREFERENCES T4, SYSCOLUMNS T5, SYSCOLUMNS T6
WHERE T1.TYPE = "U"
AND T2.TYPE = "U"
AND T3.TYPE = "F"
AND T1.ID = T4.RKEYID
AND T4.FKEYID = T2.ID
AND T3.ID = T4.CONSTID

AND T5.ID = T2.ID
AND T5.COLID = T4.FKEY4
AND T6.ID = T1.ID
AND T6.COLID = T4.RKEY4
union
SELECT CONVERT(VARCHAR(30),T1.NAME) Parent,
CONVERT(VARCHAR(20),T6.NAME) Parent_Column,
CONVERT(VARCHAR(30),T2.NAME) Child,
convert(varchar(20),T5.NAME) Child_Column,
CONVERT(VARCHAR(30),T3.NAME) Constraint_Name
FROM SYSOBJECTS T1, SYSOBJECTS T2, SYSOBJECTS T3, SYSREFERENCES T4, SYSCOLUMNS T5, SYSCOLUMNS T6
WHERE T1.TYPE = "U"
AND T2.TYPE = "U"
AND T3.TYPE = "F"
AND T1.ID = T4.RKEYID
AND T4.FKEYID = T2.ID
AND T3.ID = T4.CONSTID

AND T5.ID = T2.ID
AND T5.COLID = T4.FKEY5
AND T6.ID = T1.ID
AND T6.COLID = T4.RKEY5
union
SELECT CONVERT(VARCHAR(30),T1.NAME) Parent,
CONVERT(VARCHAR(20),T6.NAME) Parent_Column,
CONVERT(VARCHAR(30),T2.NAME) Child,
convert(varchar(20),T5.NAME) Child_Column,
CONVERT(VARCHAR(30),T3.NAME) Constraint_Name
FROM SYSOBJECTS T1, SYSOBJECTS T2, SYSOBJECTS T3, SYSREFERENCES T4, SYSCOLUMNS T5, SYSCOLUMNS T6
WHERE T1.TYPE = "U"
AND T2.TYPE = "U"
AND T3.TYPE = "F"
AND T1.ID = T4.RKEYID
AND T4.FKEYID = T2.ID
AND T3.ID = T4.CONSTID

AND T5.ID = T2.ID
AND T5.COLID = T4.FKEY6
AND T6.ID = T1.ID
AND T6.COLID = T4.RKEY6
union
SELECT CONVERT(VARCHAR(30),T1.NAME) Parent,
CONVERT(VARCHAR(20),T6.NAME) Parent_Column,
CONVERT(VARCHAR(30),T2.NAME) Child,
convert(varchar(20),T5.NAME) Child_Column,
CONVERT(VARCHAR(30),T3.NAME) Constraint_Name
FROM SYSOBJECTS T1, SYSOBJECTS T2, SYSOBJECTS T3, SYSREFERENCES T4, SYSCOLUMNS T5, SYSCOLUMNS T6
WHERE T1.TYPE = "U"
AND T2.TYPE = "U"
AND T3.TYPE = "F"
AND T1.ID = T4.RKEYID
AND T4.FKEYID = T2.ID
AND T3.ID = T4.CONSTID

AND T5.ID = T2.ID
AND T5.COLID = T4.FKEY7
AND T6.ID = T1.ID
AND T6.COLID = T4.RKEY7

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources