List all tables details of a database

To get all the table details of a specific database in MS SQL Server, use the following query.

USE [DATABASE-NAME]

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS


After you run the query use can select individual fields as per your requirement. As of me my requirement is as follows.

USE [DATABASE-NAME]

SELECT

TABLE_NAME, COLUMN_NAME,DATA_TYPE,IS_NULLABLE, ISNULL(COLUMN_DEFAULT,'') AS COLUMN_DEFAULT

FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY TABLE_NAME


To include the length of the column run the following script.

USE [DATABSE-NAME]

SELECT
OBJECT_NAME(c.OBJECT_ID) TableName
,c.name AS ColumnName
,SCHEMA_NAME(t.schema_id) AS SchemaName
,t.name AS TypeName
,t.is_user_defined
,t.is_assembly_type
,c.max_length
,c.PRECISION
,c.scale
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
ORDER BY TableName;