Select Column Information using SQL Server
Use this T-SQL to select infomation about a specific table/columns, Useful for automated scripts.
DECLARE @tablename varchar(100) SET @tablename = N'myTable' SELECT clmns.name AS [Name], usrt.name AS [DataType], ISNULL(baset.name, N'') AS [SystemType], CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length], CAST(clmns.precision AS int) AS [NumericPrecision] FROM sys.tables AS tbl INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id WHERE (tbl.name=@tablename and SCHEMA_NAME(tbl.schema_id)=N'dbo') ORDER BY clmns.column_id ASC
By the way, I worked this out using SQL Profiler. I switched on a trace and then clicked on a table to show columns. The trace then showed some SQL similar to this (more complex) and I just rendered down what I needed.
The point I am making is that SQL Profiler turned out to be a handy resource on the sys schema.
Then again it has been pointed out to me that the following is also valid:
select column_name, data_type, character_maximum_length from information_schema.columns where table_name = 'myTable'
I have been involved in IT development for the last 10 years - a lot of it around desktop applications and telecoms.