Query to retrieve column information using catalog views

General 1 Minute

The below query helps to retrieve information on columns in a table:

Select A.Name,B.Name,D.name,A.max_length,A.precision,
A.is_identity,A.is_nullable,E.[definition]
from sys.columns A inner join sys.tables B on A.object_id=B.object_id
inner join sys.schemas C on C.schema_id=B.schema_id
inner join sys.types D on D.user_type_id=A.user_type_id
left outer join sys.default_constraints E on E.parent_column_id=A.column_id and A.object_id=E.parent_object_id
where C.name='<<SchemaName>>' and B.name='<<TableName>>'

sp_columns system stored procedure can also be used to get the information.

Published by SQLWhisperer

Hello Everyone. My intention of having this site is to have one repository location for my SQL Server Learning's. Most of the information here is already available on Web but I want to have it in one global location, so I can reference whenever I need . I would be more than happy, if you find anything on this blog useful. Also, Please use your own caution when running any code on your systems.Thank you for visiting.

Published