March 15, 2004 at 5:49 pm
Some help needed.
SQL Server 2000
Besides looking at the tables one at a time Is there any way to search an entire database including every table for an existing field.
Or is there anyway to look at a field within a datbase and derive from that field all its relationships and dependencies to all other tables within that database?
Thanks
Clayton
March 15, 2004 at 6:00 pm
For the first question, if the field name is the key then searching INFORMATION_SCHEMA.COLUMNS
select * from INFORMATION_SCHEMA.COLUMNS where column_name='au_id'
The second could be solved using database diagram in Enterprise Manager.
March 15, 2004 at 7:35 pm
Though this doesn' cover field dependancies this still could provide assistance to you in mapping column values and data type issues...
-- Get columns by Table_Name
SELECT tab.name Table_Name,
col.name Column_Name,
col.colid Column_Id,
typ.name Data_Type,
col.length Length,
col.prec [Precision],
col.scale Scale,
com.text Default_Value,
obj.name Default_Cons_Name,
CASE
WHEN col.isnullable = 1 THEN 'Y'
ELSE 'N'
END is_nullable,
CASE
WHEN col.status & 0x80 = 0x80 THEN 'Y'
ELSE 'N'
END is_identity
FROM sysobjects tab,
syscolumns col LEFT OUTER JOIN
syscomments com INNER JOIN
sysobjects obj ON com.id = obj.id ON col.cdefault = com.id AND com.colid = 1,
systypes typ
WHERE tab.id = col.id
AND tab.xtype = 'U'
AND tab.name <> 'dtproperties'
AND col.xusertype = typ.xusertype
--AND col.name NOT LIKE '%_ID'
--AND col.name NOT LIKE '%ID'
--AND typ.name ='nvarchar'
--and typ.name IN ('Char','Varchar')
ORDER BY 2,4,1
--ORDER BY 4,2,1
--ORDER BY 4,5,2,1
Jbabington
Jbabington@hotmail.com
March 16, 2004 at 10:23 am
I've used this for a quick find of like columns:
select
CAST(so.name AS char(25)) 'Table Name',
sc.name 'Column Name'
from
syscolumns as sc
INNER JOIN sysobjects as so
ON sc.id = so.id
where
sc.name like 'site%'
and
so.type = 'u'
order by
so.name
and it was probably here that someone shared this RI query
SELECT SO1.name AS TablName,
SC1.name AS Col,
SO2.name AS RefTabl,
SC2.name AS RefCol,
FO.name AS FKName
FROM dbo.sysforeignkeys FK
INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id
AND FK.fkey = SC1.colid
INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id
AND FK.rkey = SC2.colid
INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id
INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id
INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id
WHERE SO2.Name = 'referral'
ORDER BY FKName, TablName, RefTabl
Hope these help a bit. You may know that sp_help <tablename> returns information that might be helpful, too.
N.O.
March 16, 2004 at 7:45 pm
I think the short answer to your question is NO.
The graph of dependicies genearatable in this way is limited to those dependencies scoped with the DRI and other referential integrity mechanisms of Sql Server 2000.
You still have to deal with the situation of Linked servers and cross database usage and many other places field usage can be hiding on the server. Dynamic sql steps, script job steps, reentrant XP, Customer DTS Transforms, Custom replication resolvers, etc.
Even if you constrained the query to an intradatabase scope then I think the queries above only cover a basic set of data usage and reference links its probably still not complete for scripts, procs, xps, jobs.
Peter Evans (__PETER Peter_)
March 17, 2004 at 9:51 am
Thanks that helps
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply