SQL query based upon object names stored within a table itself

Question

Hi,

I was wondering if there is a way of querying in MSSQL Server (2005 or 2008 or both) using table names / columns that are stored within a table themselves. Hopefully this makes sense but I imagine not so below is an example of what i mean, consider the tables:

tableCollection
----------------------------
CollectionID        ident pk
Name                varchar(30)
CollectionTypeID    int fk

tableCollectionType
---------------------------
CollectionTypeID    ident pk
Name                varchar(30)
TableName           varchar(50)
NameColumn          varchar(50)

tableCollectionLinks
---------------------------
CollectionLinkID    ident pk
CollectionID        int
LinkID              int

This is obviously a simplistic example and the data types are only there to help to show an idea of the structure (so no wasted comments on db design please!).

What I want to achieve is to be able to select the links for a collection and then using the 'TableName' and 'NameColumn' in the table 'tableCollectionType' query across the relevant tables in order to achieve a listing in 1 query rather than two (using the values returned).

An idea of the query is below:

SELECT cl.LinkID, c.Name, ct.Name, 
(
      SELECT ***THE OBJECT NAME USING 'TableName' and 'NameColumn'
) As ObjectName
FROM tableCollectionLinks cl
INNER JOIN tableCollectionType c ON cl.CollectionID=c.CollectionID
INNER JOIN tableCollectionType ct ON ct.CollectionTypeID=c.CollectionTypeID
WHERE c.CollectionID=1

Any ideas?

Thanks, Steve

Answer 1

no there's no such thing. you could use dynamic sql for this but there's really no point in doing so.

Answer 2

Take a look at the sys.tables, Sys.columns, sys.types system views.

From these 3 you might be able to see how to select the information you want.