Re: Help Required regarding Sql

[Roland Bouman]

Do you want to know this for MS SQL Server or for MySQL?

For both, the best way to do this is by using the information_schema but the information_schema is available only as of mysql 5.0.2

The information is contained in

information_schema.columns


For the current database, the query would be:
In MySQL >= 5.02

select c.table_name
, c.column_name
, c.data_type
, c.numeric_precision
...
..(lots of other properties that define the final declared column datatype)
..
from information_schema.columns
where table_schema = schema()

MySQL has a nice extension that return the datatype spec as you would enter it in ddl, so with datatype name, any parenthesis and precision/length info:

select c.table_name
, c.column_name
, c.column_type
from information_schema.columns
where table_schema = schema()

In MS SQL server, you can use

select c.table_name
, c.column_name
, c.data_type
, c.numeric_precision
...
..(lots of other properties that define the final declared column datatype)
..
from information_schema.columns

as ms sql automatically restricts it for the current database



Edited 1 time(s). Last edit at 01/26/2006 09:41AM by Roland Bouman.