SQL query to get the structure of a table of datbaseA in serverA

Question

I need to get the strucure(field names and its datatypes) of a table ,that is in databaseA of serverA

Answer 1

If your question refers to Sql Server than:

SELECT 
    C.TABLE_NAME
    ,C.COLUMN_NAME
    ,C.DATA_TYPE
    ,C.CHARACTER_MAXIMUM_LENGTH
FROM 
    INFORMATION_SCHEMA.TABLES T 
    INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON T.TABLE_NAME=C.TABLE_NAME
WHERE 
    T.TABLE_TYPE='BASE TABLE'
ORDER BY 
    C.TABLE_NAME

To access different server you have to create linkserver and than precede INFORMATION_SCHEMA in from clause with link server name and database name.

Answer 2

As Tomek pointed out, in MSSQL you can query the INFORMATION_SCHEMA database.

This also works in MySQL (as of version MySQL 5), although in some cases it can be very slow, so at the moment you are better off using the SHOW statement.