on 2009 Dec 09 3:03 PM
There seems to be some difference's between versions of SQL Anywhere on how to get a list of columns and data types for a table. This is the SQL I use:
select A.name,
C.name as datatype,
A.length,
A.prec,
A.scale,
CASE A.status WHEN 8 THEN 1 ELSE 0 END
FROM dbo.syscolumns A,
dbo.systypes C
WHERE A.id = Object_Id('MyTableNameHere')
AND A.usertype = C.usertype
ORDER BY A.colid
In some cases, it appears that it doesn't pick up all the columns for a table. Is it the join to systypes?
What SQL can I use across all versions to extract the information in the above query?
Thanks,
Brad
Request clarification before answering.
I suspect that dbo.syscolumns doesn't list columns that use custom domains. Here's an almost equivalent statement:
select c.column_name,
case c.nulls when 'Y' then 1 else 0 end as nulls,
d.domain_name,
c.width,
d."precision",
c.scale
from sys.syscolumn c join sys.sysdomain d on c.domain_id=d.domain_id
join sys.systable t on c.table_id=t.table_id
where t.object_id=Object_Id('MyTableNameHere')
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
@Reimer: Change the WHERE clause to where t.table_name='whatever' and it works for versions 5.5 through 11.0.1. Also note, if there are multiple tables with the same name but different owners, you really need to add a predicate for the owner.
User | Count |
---|---|
50 | |
9 | |
8 | |
6 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.