Whenever you need to pull a quick definition of a table in SQL Server. Here’s the SQL command (T-SQL) to get the table definition in a database:
USE [MyDB] GO SELECT COLUMN_NAME, CASE WHEN ISNULL(NUMERIC_PRECISION, 200) <> 200 THEN DATA_TYPE + '(' + CAST(NUMERIC_PRECISION AS VARCHAR(5)) + ',' + CAST(NUMERIC_SCALE AS VARCHAR(5)) + ')' WHEN ISNULL(CHARACTER_MAXIMUM_LENGTH, 0) = 0 THEN DATA_TYPE ELSE DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')' END AS DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '[MyTable]' GO
The result would be something similar to this:
COLUMN_NAME | DATA_TYPE | IS_NULLABLE |
---|---|---|
AccountNo | int(10,0) | YES |
AccountName | varchar(50) | YES |
Balance | money(10,2) | YES |
UpdateDate | datetime | NO |
You need to substitute [MyDB] and [MyTable] to the database name and table name that you’re querying. That’s all there is to it.
Further Reading
How to Get Names of All Tables in SQL Server
How to Find a Column Name in SQL Server Database
How to Show Field Values as Columns in SQL Server
Leave a Reply