sql problem - 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

sql problem

Author

Topic

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-10-13 : 13:41:32

I have a table called SONG. I would like to know how to check the columns of the SONG table using two different methods: by running the sp_columns stored procedure and by joining the catalog views sys.columns and sys.objects.

am i correct along the lines like this?

sys.sysobjects
join
sys.syscolumns

thanks

Kristen
Test

22859 Posts

Posted - 2007-10-13 : 14:54:40

Well, you can do it like that, but you would probably be better off using the INFORMATION_SCHEMA tables, for example:

SELECT TOP 10 * FROM INFORMATION_SCHEMA.TABLES
SELECT TOP 10 * FROM INFORMATION_SCHEMA.COLUMNS

Kristen

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-10-13 : 15:19:40

can you show me how to do it using the join?

thanks!

Kristen
Test

22859 Posts

Posted - 2007-10-13 : 15:23:03

Why? Did you try it? What information is there that you need that isn't available WITHOUT a JOIN?

Kristen

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-13 : 20:58:24

What you like to find doesn't need join, just query INFORMATION_SCHEMA.COLUMNS as said above.

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-10-13 : 23:27:42

oh ok i tried that and it work but I also like to learn the method in using the join method.

please guide thank you

Kristen
Test

22859 Posts

Posted - 2007-10-14 : 00:44:11

Please see Books Online for "Joining tables", and come back if you then have questions.

Kristen

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-10-14 : 11:06:55

I 'm not sure if this is correct but something like this?

select Object_Name(Object_id) As TableName ,*from sys.columns where object_id=object_id('YourTableName')

If i applied it to this example where i have order table, order_name is the column, order_id is the pk would that be right?

select order_name(order_id) as order, * from sys.columns where order_id=order_id(order)

exec SP_columns 'order'

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-14 : 11:12:32

In this case Object implies table so you just need to replace 'YourTableName' with actual table name i.e. Order:

Select Object_Name(Object_id) As TableName ,*
from sys.columns where object_id=object_id('order')

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-10-14 : 11:17:59

you mean like this?

select order(order_id) as order, * from sys.columns where order_name=order_id('order')

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-14 : 11:43:51

No. Do not replace anything other than 'YourTableName' parameter of OBJECT_ID() in WHERE clause as I shown you.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-10-14 : 11:57:23

oh ok i got you.
Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources