March 10, 2009 at 6:28 am
Hi there,
We've been having a bit of a heated debate in the office about what i'm trying to achieve. I believe it's quite possible entirely in SQL but my colleagues think i've lost the plot.
Can I use T-SQL to return a data table for a specified primary key and table name AND all of it's associated tables and values. So given a simple example of an orders table which links to products and customers using foreign key references and supplying an order id, could I get the following output without knowing the schema:
Orders.OrderID, Orders.CustomerID, Customer.CustomerID, Customer.Name, Orders.ProductID, Products.ProductID, Products.Name
I've done some googling to see if it's been done before but drawn a blank, surely it has been done before though?
Matt
March 10, 2009 at 6:44 am
yes i've done this lots of times.
for me, the core was using sp_fkeys [yourtable] to get all the direct dependancies on the table...
from there, it was building all the queries for the child tables for the specific ORDERID in this example, (if exists select ...where orderid=@myvalue)
it depends on what you want to return...just tablenames that contain references to the data related to your RODERID, oir the actual data?
Lowell
March 10, 2009 at 6:48 am
It would need to be the actual data. I'm trying to create a way to specify custom reports and this copy of the data will be copied into a data warehouse. (after some translation from cols into rows)
Thanks,
Matt
March 10, 2009 at 7:15 am
in your example above, you mentioned only specific fields, that would be grabgbed based on foreign keys:
Orders.OrderID, Orders.CustomerID, Customer.CustomerID, Customer.Name, Orders.ProductID, Products.ProductID, Products.Name
that is nothing more than a view which joins Orders,Customer and Products....
SELECT Orders.OrderID, Orders.CustomerID, Customer.CustomerID, Customer.Name, Orders.ProductID, Products.ProductID, Products.Name
FROM ORDERS
INNER JOIN CUSTOMER ON Orders.CustomerID = CUSTOMER .CustomerID
INNER JOIN PRODUCTS ON Orders.ProductID = Products.ProductID
WHERE ORDERS.ORDERID=@ORDERID
that is different than selecting stuff like:
SELECT * FROM ORDERS WHERE ORDERID=@myval
SELECT * FROM CUSTOMER WHERE CUSTOMERID IN(SELECT CUSTOMERID FROM ORDERS WHERE ORDERID=@myval)
SELECT * FROM PRODUCTS WHERE PRODUCTID IN(SELECT PRODUCTID FROM ORDERS WHERE ORDERID=@myval)
...[and so on for every dependancy table]
so what is it, specifically, you are trying to do again?
Lowell
March 17, 2009 at 3:18 am
Hi there,
Thanks for your assistance however the answer I'm looking for is a more generic one. I'm trying to use the system views to return for me all the columns and primary keys for a given database. I've made some slight modifications to a script from http://blog.sqlauthority.com/ and come up with the following
SELECT -- *
DISTINCT
FK.TABLE_NAME,
CO.COLUMN_NAME,
PK.TABLE_NAME,
PT.COLUMN_NAME,
CU.TABLE_SCHEMA,
CASE WHEN
CU.COLUMN_NAME = CO.COLUMN_NAME THEN 1
ELSE 0
END AS [IS_COLUMN_KEY]
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOININFORMATION_SCHEMA.COLUMNS CO ON FK.TABLE_NAME = CO.TABLE_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
If you run the code you'll see that it's just not right, the data i'd like to see if run on something like adventureworks (with a where FK.Table_Name = 'Sales.SalesOrderDetail' ) would be the primary key tables (SalesOrderHeader and SpecialOfferProduct) in the PK columns with their own columns (standard and key) listed in co.Column_name.
The aim of this script is to allow me to build up a sql statement which will work on any database and any table structure, and return all the associated data for a primary key. So for example given the adventure works example. I'd like the script above to return all the columns in Sales.SalesOrderDetail, (inner joined on) Sales.SalesOrderHeader and Sales.SpecialOfferProduct. I've done the bit which works out the columns for the current table (SalesOrderDetail in this example) and the generation of the DynamicSQL. I just need to get the above portion working also.
Hope thats clear, I realise it's probably not.
Matt
March 17, 2009 at 10:35 am
The problem with "all of its associated tables, columns and values" is "how far" do you want to go with it because there could be an FK pointing to another table that also have FKs and so on...
The other issue I have with this is: How useful such thing is when you could potentially retrieve **a lot** of duplicates. ??
* Noel
March 17, 2009 at 11:14 am
I only need to go one level currently, it doesn't need to be recursive. I appreciate it will contain a lot of repeating data but I've got something else in mind which will deal with that.
The query is used to take a "snapshot" of a record which will be partially warehoused in a different system.
Although I'm trying to create a coverall solution it will have limited usage on three to four databases so I'll often know what the output is going to be and that it will be manageable.
Thanks,
Matt
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy