CODEWITHSUNDEEP
sqlsql-server-2008ms-access
GibboK
GibboK

Reputation: 73908

SQL how to know the datatype for a column

I'm using MS Access and MS Sql 2008, I need get the DataType for a specific column in a single table.

Could you please post a sample of code?

Upvotes: 0

Views: 161

Answers (3)

Tim Lehner
Tim Lehner

Reputation: 15251

I'm assuming that you want to programmatically get schema metadata for your MS Access DB, as there are other answers for SQL Server. Unfortunately, MS Access doesn't offer an information_schema catalog of views (or any real analog) to query directly, so you might have to try one of the following:

Upvotes: 1

Ivan Golović
Ivan Golović

Reputation: 8832

You can also use catalog views:

SELECT  sch.name [Schema],
        tbl.name [Table],
        col.name [Column],
        typ.name [Type]
FROM    sys.columns col
JOIN    sys.tables tbl ON 
        tbl.object_id = col.object_id
AND     tbl.name = 'tableName'
JOIN    sys.schemas sch ON
        sch.schema_id = tbl.schema_id
AND     sch.name = 'schemaName'
JOIN    sys.types typ ON
        typ.system_type_id = col.system_type_id

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

Try this:

SELECT
  COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'schema'
  AND TABLE_NAME = 'tablename';

Upvotes: 3

Related Questions