Given a table A, I need to find all the tables that are in PK-FK with A and the columns in the reference. I can get the tables that have the FK relation through sysreferences or sysconstraints or sysforeignkeys but I have not been able to find out how to identify the specific column that is in the relation. Any one has any idea?
View 2 RepliesThe script below can be used to determine the reference levels of all tables in a database in order to be able to create a script to load tables in the correct order to prevent Foreign Key violations.
This script returns 3 result sets. The first shows the tables in order by level and table name. The second shows tables and tables that reference it in order by table and referencing table. The third shows tables and tables it references in order by table and referenced table.
Tables at level 0 have no related tables, except self-references. Tables at level 1 reference no other table, but are referenced by other tables. Tables at levels 2 and above are tables which reference lower level tables and may be referenced by higher levels. Tables with a level of NULL may indicate a circular reference (example: TableA references TableB and TableB references TableA).
Tables at levels 0 and 1 can be loaded first without FK violations, and then the tables at higher levels can be loaded in order by level from lower to higher to prevent FK violations. All tables at the same level can be loaded at the same time without FK violations.
Tested on SQL 2000 only. Please post any errors found.
Edit 2006/10/10:
Fixed bug with tables that have multiple references, and moved tables that have only self-references to level 1 from level 0.
-- Start of Script - Find_Table_Reference_Levels.sql
/*
Find Table Reference Levels
This script finds table references and ranks them by level in order
to be able to load tables with FK references in the correct order.
Tables can then be loaded one level at a time from lower to higher.
This script also shows all the relationships for each table
by tables it references and by tables that reference it.
Level 0 is tables which have no FK relationships.
Level 1 is tables which reference no other tables, except
themselves, and are only referenced by higher level tables
or themselves.
Levels 2 and above are tables which reference lower levels
and may be referenced by higher levels or themselves.
*/
declare @r table (
PK_TABLE nvarchar(200),
FK_TABLE nvarchar(200),
primary key clustered (PK_TABLE,FK_TABLE))
declare @rs table (
PK_TABLE nvarchar(200),
FK_TABLE nvarchar(200),
primary key clustered (PK_TABLE,FK_TABLE))
declare @t table (
REF_LEVEL int,
TABLE_NAME nvarchar(200) not null primary key clustered )
declare @table table (
TABLE_NAME nvarchar(200) not null primary key clustered )
set nocount off
print 'Load tables for database '+db_name()
insert into @table
select
TABLE_NAME = a.TABLE_SCHEMA+'.'+a.TABLE_NAME
from
INFORMATION_SCHEMA.TABLES a
where
a.TABLE_TYPE = 'BASE TABLE'and
a.TABLE_SCHEMA+'.'+a.TABLE_NAME <> 'dbo.dtproperties'
order by
1
print 'Load PK/FK references'
insert into @r
selectdistinct
PK_TABLE =
b.TABLE_SCHEMA+'.'+b.TABLE_NAME,
FK_TABLE =
c.TABLE_SCHEMA+'.'+c.TABLE_NAME
from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
on
a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and
a.UNIQUE_CONSTRAINT_NAME = b.CONSTRAINT_NAME
join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
on
a.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA and
a.CONSTRAINT_NAME = c.CONSTRAINT_NAME
order by
1,2
print 'Make copy of PK/FK references'
insert into @rs
select
*
from
@r
order by
1,2
print 'Load un-referenced tables as level 0'
insert into @t
select
REF_LEVEL = 0,
a.TABLE_NAME
from
@table a
where
a.TABLE_NAME not in
(
select PK_TABLE from @r union all
select FK_TABLE from @r
)
order by
1
-- select * from @r
print 'Remove self references'
delete from @r
where
PK_TABLE = FK_TABLE
declare @level int
set @level = 0
while @level < 100
begin
set @level = @level + 1
print 'Delete lower level references'
delete from @r
where
PK_TABLE in
( select TABLE_NAME from @t )
or
FK_TABLE in
( select TABLE_NAME from @t )
print 'Load level '+convert(varchar(20),@level)+' tables'
insert into @t
select
REF_LEVEL =@level,
a.TABLE_NAME
from
@table a
where
a.TABLE_NAME not in
( select FK_TABLE from @r )
and
a.TABLE_NAME not in
( select TABLE_NAME from @t )
order by
1
if not exists (select * from @r )
begin
print 'Done loading table levels'
print ''
break
end
end
print 'Count of Tables by level'
print ''
select
REF_LEVEL,
TABLE_COUNT = count(*)
from
@t
group by
REF_LEVEL
order by
REF_LEVEL
print 'Tables in order by level and table name'
print 'Note: Null REF_LEVEL nay indicate possible circular reference'
print ''
select
b.REF_LEVEL,
TABLE_NAME = convert(varchar(40),a.TABLE_NAME)
from
@table a
left join
@t b
on a.TABLE_NAME = b.TABLE_NAME
order by
b.REF_LEVEL,
a.TABLE_NAME
print 'Tables and Referencing Tables'
print ''
select
b.REF_LEVEL,
TABLE_NAME = convert(varchar(40),a.TABLE_NAME),
REFERENCING_TABLE =convert(varchar(40),c.FK_TABLE)
from
@table a
left join
@t b
on a.TABLE_NAME = b.TABLE_NAME
left join
@rs c
on a.TABLE_NAME = c.PK_TABLE
order by
a.TABLE_NAME,
c.FK_TABLE
print 'Tables and Tables Referenced'
print ''
select
b.REF_LEVEL,
TABLE_NAME = convert(varchar(40),a.TABLE_NAME),
TABLE_REFERENCED =convert(varchar(40),c.PK_TABLE)
from
@table a
left join
@t b
on a.TABLE_NAME = b.TABLE_NAME
left join
@rs c
on a.TABLE_NAME = c.FK_TABLE
order by
a.TABLE_NAME,
c.PK_TABLE
-- End of Script
Results from Northwind database:
Load tables for database Northwind
(13 row(s) affected)
Load PK/FK references
(13 row(s) affected)
Make copy of PK/FK references
(13 row(s) affected)
Load un-referenced tables as level 0
(0 row(s) affected)
Remove self references
(1 row(s) affected)
Delete lower level references
(0 row(s) affected)
Load level 1 tables
(7 row(s) affected)
Delete lower level references
(9 row(s) affected)
Load level 2 tables
(4 row(s) affected)
Delete lower level references
(3 row(s) affected)
Load level 3 tables
(2 row(s) affected)
Done loading table levels
Count of Tables by level
REF_LEVEL TABLE_COUNT
----------- -----------
1 7
2 4
3 2
(3 row(s) affected)
Tables in order by level and table name
Note: Null REF_LEVEL nay indicate possible circular reference
REF_LEVEL TABLE_NAME
----------- ----------------------------------------
1 dbo.Categories
1 dbo.CustomerDemographics
1 dbo.Customers
1 dbo.Employees
1 dbo.Region
1 dbo.Shippers
1 dbo.Suppliers
2 dbo.CustomerCustomerDemo
2 dbo.Orders
2 dbo.Products
2 dbo.Territories
3 dbo.EmployeeTerritories
3 dbo.Order Details
(13 row(s) affected)
Tables and Referencing Tables
REF_LEVEL TABLE_NAME REFERENCING_TABLE
----------- ---------------------------------------- ----------------------------------------
1 dbo.Categories dbo.Products
2 dbo.CustomerCustomerDemo NULL
1 dbo.CustomerDemographics dbo.CustomerCustomerDemo
1 dbo.Customers dbo.CustomerCustomerDemo
1 dbo.Customers dbo.Orders
1 dbo.Employees dbo.Employees
1 dbo.Employees dbo.EmployeeTerritories
1 dbo.Employees dbo.Orders
3 dbo.EmployeeTerritories NULL
3 dbo.Order Details NULL
2 dbo.Orders dbo.Order Details
2 dbo.Products dbo.Order Details
1 dbo.Region dbo.Territories
1 dbo.Shippers dbo.Orders
1 dbo.Suppliers dbo.Products
2 dbo.Territories dbo.EmployeeTerritories
(16 row(s) affected)
Tables and Tables Referenced
REF_LEVEL TABLE_NAME TABLE_REFERENCED
----------- ---------------------------------------- ----------------------------------------
1 dbo.Categories NULL
2 dbo.CustomerCustomerDemo dbo.CustomerDemographics
2 dbo.CustomerCustomerDemo dbo.Customers
1 dbo.CustomerDemographics NULL
1 dbo.Customers NULL
1 dbo.Employees dbo.Employees
3 dbo.EmployeeTerritories dbo.Employees
3 dbo.EmployeeTerritories dbo.Territories
3 dbo.Order Details dbo.Orders
3 dbo.Order Details dbo.Products
2 dbo.Orders dbo.Customers
2 dbo.Orders dbo.Employees
2 dbo.Orders dbo.Shippers
2 dbo.Products dbo.Categories
2 dbo.Products dbo.Suppliers
1 dbo.Region NULL
1 dbo.Shippers NULL
1 dbo.Suppliers NULL
2 dbo.Territories dbo.Region
(19 row(s) affected)
CODO ERGO SUM
Create table #tblActvity
(
activityIDvarchar (50),
activityParentIDvarchar(50)
)
Insert into #tblActvity
SElect '1',Null
[Code] ...
--If I pass activityId 3 or 2 or 4 it should return 0 as none of the activity is circular but If I pass 5, 6 or 7 it should return 1 as they have circular reference....
I need a sql qry which will require to find a circular reference in it.....
As in above sample of data ,If I pass activityId 3 or 2 or 4 to qry it should return 0 as none of the activity is circular but If I pass 5, 6 or 7 it should return 1 as they have circular reference....
How can I list the stored procedures and user-defined functions that reference a given column? I could search ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES for '%MyColumnName%' but MyColumnName is not always unique.
Thanks.
One of our Oracle Tables changed and I am wondering if there's any way that I can query all of our Stored Procedures to try and find out if that Oracle Table Name is referenced in any of our SQL Server Stored Procedures OPENQUERY statements?
View 2 Replies View RelatedI am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:
Error system.NullReferenceException: Object refrence not set to an instance reference.
In PreExecute section
TextReader = new system.io.streamreader(" file name")
In the CreateNewOutputRows:
dim nextLine as string
nextLine = textReader.ReadLine
[code]...
is there something which i did not install or what can be the error?
I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you
View 4 Replies View RelatedI have 3 database say
db1
db2
db3
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
Hi y'all
Does anybody know reference or pdf file or free e-learning on the web?
DECLARE Loan_cursor CURSOR FOR
SELECT Loan_No,store
FROM loan
WHERE maturity_date > '2001-04-30' and loan_no like 'ABL%'
OPEN Loan_cursor
-- Perform the first fetch.
FETCH NEXT FROM Loan_cursor
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
Declare @LoanNo varchar(12)
** Set @LoanNo = Loan_No
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM Loan_cursor
END
CLOSE Loan_cursor
DEALLOCATE Loan_cursor
when l run the cursor l get the error
Server: Msg 207, Level 16, State 3, Line 15
Invalid column name 'Loan_No'.
.
If l reference it as Set @LoanNo = LoanTable.Loan_No l get the error
Server: Msg 107, Level 16, State 3, Line 15
The column prefix 'loan' does not match with a table name or alias name used in the query.
All l'm trying to do is to compare the loan number that l get from the cursor to the value in the loans table. Maybe reference is a better word
Hi friends,
Just i modified one sub stored procedures which is not getting affected in the main stored procedures
Create procedure main
as
begin
set nocount on
--First sp
exec Data_transfer_sp
--Second sp
Exec Clearance_sp
set nocount off
end
In data_transfer_sp,i have commented the select statement
but still iam use to the select result while execution the main sp.
Note:I have compiled the data_transfer_sp after making comment
.Txs in advance
Hi All ...
WITH myCTE (x,y,z) AS (SELECT x,y,z, from myTable)
SELECT x,y,sum(z) from myCTE
SELECT y,z,sum(x) from myCTE
the second SELECT fails, and says invalid object name. does the CTE go out of scope after i reference it once?
never mind the semantics of what I am SELECTing, I just want to be able to reference the CTE more than once in my SP
am I trying to use the CTE in a way that was not intended?
Hi All ...
WITH myCTE (x,y,z) AS (SELECT x,y,z, from myTable)
SELECT x,y,sum(z) from myCTE
SELECT y,z,sum(x) from myCTE
the second SELECT fails, and says invalid object name. does the CTE go out of scope after i reference it once?
never mind the semantics of what I am SELECTing, I just want to be able to reference the CTW more than once in my SP
am I trying to use the CTE in a way that was not intended?
hi ,
can any body help me that how can i use my .net dll into my rdl file.
thanks
Gorla
in this code ,whtat is the references of
RelationalDataSourceView
and also
what are the references of these?
OleDbConnection
OleDbCommand
OleDbDataAdapter
Can anyone see why I would get the 'Object Referenece not set to an instance of an object error in the following code?
It happens on this line:
MyAdapter.SelectCommand = New SqlCommand(SelectStatement, MyConnection) 'Populate the text boxes from database for alumni fields.
Dim MyAdapter As SqlDataAdapter
Dim MyCommandBuilder As SqlCommandBuilder
Dim DetailsDS As DataSet
Dim Row As DataRow
Dim SelectStatement As String = "Select ClientID,ClassYear,HouseName,CampusName,EducationMajor,GraduationDate FROM tblclient Where [ClientID]=" & _
ClientIDSent
Dim ConnectStr As String = _
ConfigurationManager.ConnectionStrings("TestConnectionString").ConnectionString
Dim MyConnection As SqlConnection = New SqlConnection(ConnectStr)
MyAdapter.SelectCommand = New SqlCommand(SelectStatement, MyConnection)
MyCommandBuilder = New SqlCommandBuilder(MyAdapter)
MyAdapter.Fill(DetailsDS, "tblClient")
Row = DetailsDS.Tables("tblClient").Rows(0)
ClassYearText.Text = Row.Item("Classyear").ToString()
HouseNameText.Text = Row.Item("HouseName").ToString()
CampusNameText.Text = Row.Item("CampusName").ToString()
EducationMajorText.Text = Row.Item("EducationMajor").ToString()
GraduationDateText.Text = Row.Item("GraduationDate").ToString()
Hi,
I'm looking for a good reference guide online as I am more used to mysql (and stil quite limited vocab at that)
I create table outside of a database by accident and I'm now looking for the sql syntaxt for moving tables, but I can't find it anywhere?
most simple guides don't seem to provide the syntax to do this. :(
does any one know any good pages to get some sql commands
and explainations thanks
Hi,
I am access SQL 7 via ADO in some ASP pages. My database objects were created under a user name aliased to the dbo. As a result, when I have my client logon to make a connection, I have to preface all my object references with the name of the owner, i.e., Select * from mydboname.people . Is there a way to avoid having to prefix all my transact sql statements with the owner?
thanks,
steve
I have two tables that are reference to each other by foreign key, now I would like to alter the table so the it doesn't reference each other any more. What is the syntax to alter a table so that the fk field doesn't reference a table anymore. Thanks in advance for the help.
View 1 Replies View RelatedWhat would you recommend for a good MDX book ?
specifically, for syntax, data structures and sample code.
thx
C
As I posted earlier, I am relatively new to TSQL. I have been given a few books:
O'Reilly SQL on SQL Server 2005
O'Reilly SQL in a Nutshell
Mastering SQL Server 2005 - Sybex
Beginning SQL Server 2005 Programming - Wrox
I have a copy of SQL2005 Developers Edition on a development server. So I think I have some decent tools at my disposal.
I was wondering if there are any other sites or forums that might be suggested for learning SQL programming? Right now, I am just making stuff up and following examples in books, etc. I would like to find a site where there are beginner level projects for no other purpose than educational. Something where I can get more hands on of the design and programming aspects. Just to practice.
Thanks,
grinch
i have this code,
CREATE TABLE s
(sno VARCHAR(5) NOT NULL PRIMARY KEY,
name VARCHAR(16),
city VARCHAR(16)
)
CREATE TABLE p
(pno VARCHAR(5) NOT NULL PRIMARY KEY,
descr VARCHAR(16),
color VARCHAR(8)
)
CREATE TABLE sp
(sno VARCHAR(5) NOT NULL REFERENCES s,
pno VARCHAR(5) NOT NULL REFERENCES p,
qty INT CHECK (qty>=0),
PRIMARY KEY (sno, pno)
)
when i run this query i get an error saying:
Column 's.sno' is not the same length as referencing column 'sp.sno' in foreign key 'FK__sp__sno__5EBF139D'. Columns participating in a foreign key relationship must be defined with the same length.
can anyone assist me what to be done?
Hi ,
I have the following structure:
Item table
----------
ItemId
ItemName
Item Transaction Table
----------------------
TransactionId
GiverItemId
SenderItemId
the data is somewhat like this:
Item table
__________
1abc
2xyz
3pnr
4rew
5dds
6djs
7dsf
Item Transaction table
---------------------
112
224
343
437
Now i have a reauirement to build a stored proc in which all the transactions starting from one transaction like, if i want to know the chain for item no 2 it shall give the following result:
24
43
37
if i want to know the chain for item no 3 then it shall give following
37
if i want to know the chain for item no 1 then it shall give following
12
24
43
37
Please help.. Its urgent.....
Thanks
I am teaching myself XML and was wondering if anyone knows
any good books to gently breaking me into XML.
Kind Regards
Rob
I need to parse SQL statements directly and extract each segmentindividually. Is there a way reference the Microsoft SQL Parserdirectly from VB.Net?Thanks!*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 6 Replies View Related
Forgive me for my programming ignorance, but how to i find the microsoft.sqlserverce.samples.accesssync.editdata reference?
Thanks
Hello. I'm getting an error - "Object reference not set to an instance of an object" when running an SSRS report with graphs. It only happens when the returned dataset is empty (based on parameters set by the user, it is possible to return no rows). I've tried using the NoRows property (I entered "No Data" in the property box), but I still get the same error. Is there another way to account for an empty dataset in charts?
Thanks,
Marianne
I would like to use a custom build class library written in C# inside of the vb script. Does anyone know how to add the reference to the scriptComponent project once you open script through design script button?
By the way I am using visul studio 2005.
Thanks!
Hello everyone !
Given a UDT, is there any way to get a reference to the table where the specific instance is running ?
IE: Let's suppose we have defined a UDT named UDTPoint; now we define two tables: ATable & BTable, wich both have one column that is defined as UDTPoint.
When an insert/update/delete operation on ATable or BTable occurs, the UDTPoint class needs to verify in which context it is running (ATable or BTable) before doing operations on data.
Is there any way to achieve that ?
Thanks
Giovanni
Hi,
I wanted to add a reference to an assembly in my report (not a DLL, but an EXE). As I see there is no option to select an assembly which is an EXE. Firstly, is it possible to add a reference to an EXE (I guess not)? Secondly, If no, why is it not possible?
Shyam
Hi,
Our tables have a reference field which users can enter their own references into.
However, they can choose to have the system calculate the next reference number.
The reference can contain any characters from a-z, A-Z and 0-9.
In our system, reference AAAA is greater than reference ZZZ; the next reference after AAAA is AAAB.
How would I calculate the greatest current reference in the table?
Hi:
I am bulding a report in VS 2005 using the reporting services. I like to know how to add a new reference to my report?
ty
Ben