March 18, 2002 at 1:27 pm
I would like to find out the characteristics of a table. The only issue is I would prefer to stay within my database and not require access to the sys tables in the master DB. I think that makes sense. If not let me know.
Essentially what I am trying to determine is if the column is a varchar or int. I want to to be able to urn a query that is independent of the ' or I will have to determine them on the fly.
Thanks,
Neil
March 18, 2002 at 1:56 pm
Check out the view called infoschemacolumns.
Andy
March 18, 2002 at 1:58 pm
I don't seem to have that view. Do you have it available?
Thanks,
Neil
March 18, 2002 at 2:10 pm
March 18, 2002 at 2:15 pm
Do I have a way to view the TSQL. I am trying to join the syscolumns, sysobjects and systypes now 🙂
Neil
March 18, 2002 at 3:04 pm
After about 2 hours I have come up with this:
select c.name AS ColumnName, C.Type, T.Name
from sysobjects o, syscolumns c, systypes t
where o.id = c.id
AND o.type = 'U'
AND c.xtype = t.xtype
March 18, 2002 at 4:10 pm
Looks about right, but the system tables could change in an upgrade. Information_schema views are standard views that will stay the same.
Steve Jones
March 18, 2002 at 4:40 pm
I guess, but how can I limit my query. I only want to query for a specific table and specific columns.
March 22, 2002 at 4:34 pm
and o.name= 'addresses' --Add this condition to the query above. Replace 'addresses' with the tablename u want to query. So your query would look like :
select c.name AS ColumnName, C.Type, T.Name
from sysobjects o, syscolumns c, systypes t
where o.id = c.id
AND o.type = 'U'
AND c.xtype = t.xtype
and o.name= 'addresses'
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
March 22, 2002 at 5:26 pm
I wanted to know how to limit the view. I know how to limit my query.
Neil
March 23, 2002 at 6:52 am
If you are saying you need to limit the query performed when someone does
SELECT * FROM myView
Then you will need to add the o.[Name] AS TableName in you views output selection so you can do
WHERE TableName = 'table1'
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply