GeekZilla
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.
Comments
Damien Guard
said:
Selecting directly from the system tables is unsupported by Microsoft and is exactly why they created the sp_help stored procedure and it's associates.
[)amien
Damien Guard
said:
Alternatively SQL 2005 adds metadata support for tables and columns using the syntax:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'Products';
Where Products is the table you want.
[)amien
Jason
said:
There is a built in stored procedure called sp_columns that takes a tablename as a parameter. this returns all information for the given table
Example:
sp_columns Employees
nikhil
said:
Thanks Jason, This is what i was looking for
