Query to get field name with datatype and size

[Tor_Fey]

Does anyone have a SQL query to retrieve field names with datatype and size from all tables within an access database?

I was hoping to use something like the below code... But, I want the SQL code to know the current database and tables without me having to specify them

SELECT column_name as 'Column Name', data_type as 'Data Type',
character_maximum_length as 'Max Length'
FROM information_schema.columns
WHERE table_name = 'tblUsers'

In the following example, the code can be just cut and paste from one database to another without having to change anything, and this is what i would like to achieve with the new SQL code to query field names with data types and size.

SELECT MSysObjects.name, MSysObjects.DateCreate, MSysObjects.DateUpdate
FROM MSysObjects
WHERE (((MSysObjects.name) Not Like '~*' And (MSysObjects.name) Not Like 'MSys*') AND ((MSysObjects.type) In (1,4,6)))
ORDER BY MSysObjects.DateUpdate DESC;

Your help is always appreciated

Kind regards
Tor Fey

 

[Minty]

Try something like

SELECT T.Name                   AS TableName,
       Schema_name(T.schema_id) AS SchemaName,
       C.Name                   AS ColumnName,
       Ty.Name                  AS ColumnDataType,
       C.is_nullable            AS IsNullAble,
       C.is_identity            AS IsIdentity
FROM   sys.tables T
       INNER JOIN sys.columns C
               ON T.OBJECT_ID = C.OBJECT_ID
       INNER JOIN sys.types Ty
               ON C.system_type_id = Ty.system_type_id
WHERE  T.is_ms_shipped = 0
ORDER  BY T.name

 

[Isaac]

This should not be in the sql server forum

 

[Tor_Fey]

Hi Minty;

I tried this code and get the following error:

Kind Regards
Tor Fey

 

[Minty]

This should not be in the sql server forum

Hang on - I thought you wanted this for SQL server - your code is for SQL and you state

I was hoping to use something like the below code... But, I want the SQL code to know the current database and tables without me having to specify them

SELECT column_name as 'Column Name', data_type as 'Data Type',
character_maximum_length as 'Max Length'
FROM information_schema.columns
WHERE table_name = 'tblUsers'

Although I missed the bit at the beginning that says
datatype and size from all tables within an access database?

In which case you need need to use the TableDefs Collection.

Try this https://www.devhut.net/2010/06/10/ms-access-vba-list-table-fields/

But loop through it.