finding foreign key info

Hi,
Is there a way to find out if a column on a table is a foreign key, what
table it references and what column on that table it is referencing. All of
my foreign keys are a single column, which should make things easier.
Any help would be greatly appreciated.
Regards,
NedYou can get this from a couple of INFORMATION_SCHEMA views, e.g.
select
pk_ccu.table_name as PK_Table,
pk_ccu.column_name as PK_Column,
fk_ccu.table_name as FK_Table,
fk_ccu.column_name as FK_Column
from
information_schema.constraint_column_usage pk_ccu
join information_schema.referential_constraints rc on
pk_ccu.constraint_name=rc.unique_constraint_name
join information_schema.constraint_column_usage fk_ccu on
rc.constraint_name=fk_ccu.constraint_name
where
fk_ccu.table_Name='table_in_question'
and fk_ccu.column_name='column_in_question'
Ned wrote:
> Hi,
> Is there a way to find out if a column on a table is a foreign key, what
> table it references and what column on that table it is referencing. All
of
> my foreign keys are a single column, which should make things easier.
> Any help would be greatly appreciated.
> Regards,
> Ned
>|||Thanks Trey,
Worked like a charm.
Regards,
Ned
"Trey Walpole" <treyNOpole@.comSPAMcast.net> wrote in message
news:exXjWn0rFHA.460@.TK2MSFTNGP15.phx.gbl...
> You can get this from a couple of INFORMATION_SCHEMA views, e.g.
> select
> pk_ccu.table_name as PK_Table,
> pk_ccu.column_name as PK_Column,
> fk_ccu.table_name as FK_Table,
> fk_ccu.column_name as FK_Column
> from
> information_schema.constraint_column_usage pk_ccu
> join information_schema.referential_constraints rc on
> pk_ccu.constraint_name=rc.unique_constraint_name
> join information_schema.constraint_column_usage fk_ccu on
> rc.constraint_name=fk_ccu.constraint_name
> where
> fk_ccu.table_Name='table_in_question'
> and fk_ccu.column_name='column_in_question'
>
> Ned wrote:

Finding all references to a column

I have a task to where I need to move a column from one table to
another. I want to be sure I update any view, stored procedure,
trigger, etc. that references the column. I simply want a query that
will report the related objects and then I will update them manually
but before I go and try and figure out how to do this by querying the
sys tables is there an sp_sproc that will do this?[posted and mailed, please reply in news]

rnewman (newmanr19@.yahoo.com) writes:
> I have a task to where I need to move a column from one table to
> another. I want to be sure I update any view, stored procedure,
> trigger, etc. that references the column. I simply want a query that
> will report the related objects and then I will update them manually
> but before I go and try and figure out how to do this by querying the
> sys tables is there an sp_sproc that will do this?

The best way is to build the database from scripts, with the column
reomved, and then look through all errors you get.

You can also run this query:

select object_name(id)
from sysdepends
where depid = object_id('tbl')
and col_name(depid, depnumber) = 'col'
order by 1

However, this may not be reliable. If you can be confident that all
procedures abd views have been created/altered after the table was
created, this will work. But if you have dropped the table and replaced
with a new version, or you loaded stored procedures before you created
the table, the dependency information will be incomplete.

Note: while the sysdepends tables is documented in Books Online,
the usage of the depnumber as column id is undocumented, and may
be subject to change without notice.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Find all references to a database

Is there a way to scan all procedures, functions and triggers in an SQL
server for references to a specific database?
Thanks
Tom G.The following technique isn't perfect but will identify most objects. You
could also script the textual objects to a file and use a find command.
USE MyDatabase
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%SomeDatabase%'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Groszko" <newscorrespondent@.charter.net> wrote in message
news:emfU1rr1DHA.1184@.TK2MSFTNGP10.phx.gbl...
> Is there a way to scan all procedures, functions and triggers in an SQL
> server for references to a specific database?
> Thanks
> Tom G.
>|||The solution I have come up with is:
Create a table like this:
create table PAFCONVERSION (
DBNAME sysname not null,
OBJECTNAME sysname not null,
OBJECTYPE char(2) null,
constraint PK_PAFCONVERSION primary key (DBNAME, OBJECTNAME)
)
Run this in query analyzer, copy the output pane to the execution pane and
run it.
SET NOCOUNT ON
USE master
go
DECLARE DBCURSOR CURSOR FAST_FORWARD FOR
SELECT NAME FROM SYSDATABASES ORDER BY NAME FOR READ ONLY
DECLARE @.DBNAME SYSNAME
OPEN DBCURSOR
FETCH DBCURSOR INTO @.DBNAME
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE @.DBPROCESS varchar(8000)
SET @.DBPROCESS = 'USE ' + @.DBNAME + CHAR(10) + 'GO' + CHAR(10)
+ 'INSERT SDRMONITOR.DBO.PAFCONVERSION (DBNAME, OBJECTNAME, OBJECTYPE)' +
CHAR(10)
+ 'SELECT DISTINCT ''' + @.DBNAME + ''', SYSOBJECTS.NAME, SYSOBJECTS.XTYPE'
+ CHAR(10)
+ 'FROM syscomments' + CHAR(10)
+ 'JOIN SYSOBJECTS ON (SYSCOMMENTS.ID = SYSOBJECTS.ID)' + CHAR(10)
+ 'where text like (''%what you are looking for%'') ' + CHAR(10)
+ 'ORDER BY SYSOBJECTS.NAME, SYSOBJECTS.XTYPE' + CHAR(10)
+ 'GO' + CHAR(10)
SELECT @.DBPROCESS
FETCH DBCURSOR INTO @.DBNAME
END
CLOSE DBCURSOR
DEALLOCATE DBCURSOR
GO
"Tom Groszko" <newscorrespondent@.charter.net> wrote in message
news:emfU1rr1DHA.1184@.TK2MSFTNGP10.phx.gbl...
> Is there a way to scan all procedures, functions and triggers in an SQL
> server for references to a specific database?
> Thanks
> Tom G.
>