SQL Server --- Query that will display the columns in the table along with their data-types and their position in the table they are in..

Sys tables are the source of information for any metadata related queries. This stored procedure will take table name and schema name as an input and will display all the columns, their data types and their position in the table. Example run is for the Adventureworks database, on HumanResources.Employee table.

CREATE PROC getAllColumns(@tablename VARCHAR(130),@schemaname VARCHAR(130)) AS

BEGIN 

select DISTINCT SC.name [Columnname],STS.name [Datatype],SC.column_id [Position]

from sys.columns SC

INNER JOIN sys.types STS

ON SC.user_type_id=STS.user_type_id

INNER JOIN sys.tables ST

ON SC.object_id = ST.object_id

WHERE ST.name = @tablename AND ST.schema_id = SCHEMA_ID(@schemaname)

ORDER BY SC.column_id

END

EXEC getAllColumns 'Employee','HumanResources'