March 31, 2005 at 8:12 am
Hi - you supplied me w/ some EXCELLENT SQL yesterday - used to dynamically render the parameters necessary to build DEFAULT Constraints:
select user_name(uid) as Owner,
O.Name as TableName,
C.Name as ColName,
Object_name(C.cDefault) as ConstraintName,
substring(Text, 2, len(Text) - 2) as DefaultValue
from dbo.SysComments CM
inner join dbo.SysColumns C ON CM.id = C.cDefault
inner join dbo.SysObjects O on C.id = O.id and O.XType = 'U'
order by O.Name, C.Name
ALTER TABLE [Owner].[TableName] ADD CONSTRAINT [Prefix + TableName + ColumnName + Increment] DEFAULT DefaultValue FOR [ColumnName]
NOW - I need SQL to dynamically render the parameters necessary to build Identity cols, PK's, FK's, and Index's not included in part of the PK FK gen.
THX in advance!
March 31, 2005 at 9:16 am
This will get you started again :
use my 3rd post
March 31, 2005 at 9:22 am
Foreign keys :
SELECT TOP 100 PERCENT PTbl.name AS PkTableName, PCol.name AS PkColName, FTbl.name AS ConstraintName, dbo.sysobjects.name AS FkTableName,
FCol.name AS FkColName
FROM dbo.sysobjects INNER JOIN
dbo.syscolumns FCol INNER JOIN
dbo.sysobjects FTbl INNER JOIN
dbo.sysforeignkeys FKS ON FTbl.id = FKS.constid INNER JOIN
dbo.syscolumns PCol ON FKS.rkeyid = PCol.id AND FKS.rkey = PCol.colid INNER JOIN
dbo.sysobjects PTbl ON PCol.id = PTbl.id ON FCol.id = FKS.fkeyid AND FCol.colid = FKS.fkey ON dbo.sysobjects.id = FCol.id
ORDER BY PTbl.name, dbo.sysobjects.name, FCol.name
March 31, 2005 at 9:48 am
PK, identity, indexes
SELECT O.Name AS ParentName, CASE RTRIM(O.XType)
WHEN 'U' THEN 'TABLE' WHEN 'V' THEN 'VIEW' WHEN 'TF' THEN 'TABLE FUNCTION' ELSE 'NOT POSSIBLE' END AS ParentType, i.name AS IdxName,
C.name AS ColName, SK.Keyno, i.id, USER_NAME(O.uid) AS Owner, I.indid, CASE WHEN I.indid BETWEEN 1 AND 254 AND (I.status & 2048 = 2048 OR
I.Status = 16402 AND O.XType = 'V') THEN 1 ELSE 0 END AS IsPK
FROM dbo.sysobjects o INNER JOIN
dbo.sysindexes I ON o.id = i.id INNER JOIN
dbo.SysIndexKeys SK ON I.id = SK.id AND I.indid = SK.indid INNER JOIN
dbo.SysColumns C ON SK.id = C.id AND SK.Colid = C.Colid
WHERE (O.XType = 'tf' AND NOT LEFT(C.Name, 1) = '@' OR
O.XType 'tf') AND LEFT(i.name, 8) '_WA_Sys_' AND o.status >= 0
ORDER BY O.Name, I.Name, SK.Keyno
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply