I was under the impression that the DESCRIBE operation was an ANSI standard, which would provide a standard way for viewing the definition of a table. It turned out after a few failed attempts that this is not the case. Every database vendor is free to provide their own implementation to achieve the same functionality that Oracle DESCRIBE command provides.
In the world of SQL Server you have quite a few options. SQL Server provides a set of metadata tables that can be used to gather the metadata information about any SQL Server object, including tables. To make life easier, you have quite a few system VIEWS that you can use to extract that information from an SQL Server database, such as, INFORMATION_SCHEMA.TABLES, INFORMATION_SCHEMA.VIEWS, INFORMATION_SCHEMA.COLUMNS, and so on.
SQL Server also provides a comprehensive set of System Stored Procedures on top of these views and tables to extract the metadata information of a database object. Such as, sp_columns, sp_help, etc.
For instance, if you run this command
exec sp_columns MY_TABLE_NAME
It will give you the details of all the columns in MY_TABLE_NAME table with their data types, null ability, length, etc. To get the details of a table, another stored procedure can be used to get the details.
exec sp_help MY_TABLE_NAME
This provides complete details of the table MY_TABLE_NAME including table type, all columns and their types, key columns, indexes, etc, etc.
This is very helpful indeed, however, I found that rather too much of information and what I really needed was simply the names of all the columns in the table and their data type details. The INFORMATION_SCHEMA.COLUMNS view was looking ideal for my scenario. To make things even simpler I created the following query to extract the columns and data type information from this view.
SELECT C.COLUMN_NAME, C.IS_NULLABLE,
C.DATA_TYPE + '(' +
CASE WHEN C.CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN Cast(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))
ELSE CASE WHEN C.NUMERIC_PRECISION IS NOT NULL
THEN Cast(C.NUMERIC_PRECISION AS VARCHAR(5)) + ',' + Cast(C.NUMERIC_SCALE AS VARCHAR(5))
ELSE Cast(C.DATETIME_PRECISION AS VARCHAR(5))
END
END + ')' AS 'DATA_TYPE'
FROM INFORMATION_SCHEMA.Columns C
WHERE table_name = 'MY_TABLE_NAME'
All I am doing here in this Query is extracting that data that I need and formatting the extracted data to get a better presentation.
There is still some more information regarding the columns metadata in this view but I am not interested in that for now. Also if I need more information, I can always go and join this view with other views provided by SQL Server to gather the required information.
I hope that will be helpful for you if you are looking for something similar to extract metadata from SQL Server.
