Returning Column Attributes with SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Returning Column Attributes with SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-24 : 10:39:22

OrionDark writes "Is it possible to set up a query in such a way that the underlying tablenames are returned with the query?
Example: I have a query I want to run. This query will bang against two tables, simply linked by a Primary Key/Foreign Key relationship.

I would like to set up a query in such a way so that the names of the underlying tables that each column is returned from comes back to me.

Sure,its easy if I know ahead of time what the tables will be. I can just wrap a few columns to return a table map.

But... I know the SQL Engine uses something to map to the tables with the SQL statement I send to it. Is there any way to dynamically get what columns came from what tables?

Thanks!!"

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-24 : 10:56:44

We're talking about SELECT *, aren't we...

Also, don't you have to know what you're selecting anyway?

I mean we could go dynamically crazy....but letting us know why you need to do this would be an interesting discussion....

And I know of no way to do that...you're looking for a method or something...right?

Brett

8-)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-05-24 : 10:57:32

[code]select distinct table_name
from information_schema.columns
where column_name = <your_column>[/code]

oriondark
Starting Member

2 Posts

Posted - 2004-05-25 : 10:12:56

Hmm... Let's see... the reason I want to do this is to be able to create some intrinsic data validation on inputs from queries. Inputs you say? Well, not from the queries themselves, but from the datatable generated from running the query. I haven't gotten to stripping out the join information yet, but in essence, the idea is to be able to grab all the table information about particular fields.. go back to the database, return the data requirements of the fields (type, length, etc, as in the 3rd post) from the tables used in the query. Push that information into a non-dynamic format where it can be tweaked as business rules require, then generate a GUI layer that can accomadate(sp?) data entry. Further extension would be able to accomadate things other than entry (deletions, etc), since I can grab Primary Key and Increment info from the database...
Sound plausible? or should I rough in a test case to make things a bit clearer?

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-25 : 13:30:04

I'm think (well, sometimes) I understand...

soemthing like:



USE Northwind
GO

DECLARE @ColList varchar(8000), @SQL varchar(8000), @TABLE_NAME sysname

DECLARE myCursor99 CURSOR 
FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @TABLE_NAME

WHILE @@FETCH_STATUS = 0
  BEGIN
	SELECT @ColList = COALESCE(@ColList + ', ', '') + 
   	  '[' + CAST(COLUMN_NAME AS sysname) + ']'
	  FROM INFORMATION_SCHEMA.Columns 
	 WHERE TABLE_NAME = @TABLE_NAME
	SELECT @SQL = 'DROP PROC [SEL_' + @TABLE_NAME + ']'
	EXEC(@SQL)
--	SELECT @SQL
	SELECT @SQL = 'CREATE PROC [SEL_' + @TABLE_NAME + '] AS SELECT ' + @ColList + ' FROM [' + @TABLE_NAME + ']'
--	SELECT @SQL
--	EXEC(@SQL)
	
	SELECT @ColList = Null

	FETCH NEXT FROM myCursor99 INTO @TABLE_NAME
  END

CLOSE  myCursor99
DEALLOCATE myCursor99

Brett

8-)

oriondark
Starting Member

2 Posts

Posted - 2004-06-03 : 10:19:15

Thanks for the replies, everyone!!
But on to the task.
The reason I want to do this is to do some class and dataaccess modeling based on specific queries.
I have something right now that crawls through the schema info on a particular table (much like what Brett suggested), and returns the info I need (datatype,nullable,length, etc).
This in turn is changed into a class (VB or C# for right now) that holds all this data as properties (with the correct SQL to development language datatypes).
This is turn, is used to generate a variety of things such as Web Controls, Web Pages, stored procs, etc.
Now, all I need to do is add business logic for my updates, and Gui logic for my display (which cannot be generated).
So, I'd like to be able to point a SQL statement with multiple tables at this 'generator' to be able to spit out a class that can consume and use the data appropriately.
There are a few things that I am still trying to be able to do this, but mostly it revolves around parsing the SQL statement manually.
SELECT example:
Like so: Anyalyze the qualifiers on the Select statement to figure out the tables used (problem here is if a table is aliased in the From section, you have to get that too). Use something like Brett's SQL for getting all the Schema info from each table/field pair. Then I can pass this into my generator, and I'd have a Smart object that builds a stored proc and points the object at it, and understands all the types implicitly (for GUI modeling, such as formatting dates).
Anyway, that's a brief sketch of what I am trying to do.
Any other suggestions?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources