How can I querey by col_id? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

How can I querey by col_id?

Author

Topic

Prestidigitator
Starting Member

16 Posts

Posted - 2007-10-11 : 12:26:18

If I do a select * from _schema where table_name = "xyz"

I get a list that shows:
table_name, colid, name, etc, etc, etc

What I am wondering is how to write a query where I could use the table colid in place of the table names.

So for example if I wanted to execute the following query:


SELECT premium_payment_individual_id, premium_payment_id, assigned_number, identification_code_id 
FROM premium_payment_individual

If the colid for those table column names were 3 8 9 and 11 is there a way to construct it so it was something like:


SELECT colid.3, colid.8, colid.9, colid.11 
FROM premium_payment_individual

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-11 : 12:30:07

No. You can not.

Anyway, what is the purpose of doing this?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Prestidigitator
Starting Member

16 Posts

Posted - 2007-10-11 : 13:00:17

Well the purpose is that our schema uses some really long column names like receiver_account_number_qualifier_id

And I am getting tired of typing out all the individual names when I do a query in Query analyzer....

Getting old! Has to be a better way!

Been there, done that, looking for easy!

Kristen
Test

22859 Posts

Posted - 2007-10-11 : 15:13:36

"I am getting tired of typing out all the individual names when I do a query in Query analyzer...."

QA will happily give you a list of All (say) the columns in a given table - as an alternative to "SELECT *", for example.

Personally, we have an Sproc [called "KLC" ("Kristen's List Columns")] that generates lists of column names, SProc parameters, FK JOINs, and so on in "oven ready form" so we don't have to type no stinking code!

Kristen

Prestidigitator
Starting Member

16 Posts

Posted - 2007-10-11 : 18:19:51

Well yes, I can Select * but I don't want all the columns.

I want 10 columns out of about 30+

And typing the 10 names is a pain in the you know what!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-11 : 18:25:25

Right-click on the table in EM and choose "Script Table as" --> "Select to". It will give you all the columns which it will then be very easy to delete the ones you don't want. You can also look at the columns in EM and drag them over one-by-one. Sure beats typing.
Jim

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-12 : 02:26:36

or try to simulate this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53384

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources