SELECT
c.name 'Column Name',
t.Name 'Data type',
c.max_length 'Max Length',
c.precision ,
c.scale ,
c.is_nullable,
ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM
sys.columns c
INNER JOIN
sys.types t ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN
sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
c.object_id = OBJECT_ID('TableName')
Note: If you are using nvarchar as datatype, it will give you another column with same name but datatype of sysname. Also all the Max length for nvarchar datatype will be shown as double of original value.
Example:
Let's create a table and see how it work with nvarchar and varchar datatype to show it.
CREATE Table ContactAddress
(ContactID int IDENTITY(1,1) Primary Key,
FullName varchar(50),
Address1 varchar(40),
Address2 varchar(40),
City varchar(20),
Zip varchar(5),
[State] Char(2),
Country Varchar(30)
)
--And Create another table with similar structure but this time change zip data type from varchar to nvarchar
CREATE Table ContactAddress1
(ContactID int IDENTITY(1,1) Primary Key,
FullName varchar(50),
Address1 varchar(40),
Address2 varchar(40),
City varchar(20),
Zip nvarchar(5),
[State] Char(2),
Country Varchar(30)
)
And here's the result after you run the query:
You will also notice that their is another column "Zip" with sysname datatype.
