Find column data types using sys.types and sys.syscolumns

11 September,2013 by Jack Vamvas

Question: How can I find the data types name for a sql column using a query?

Answer: Use the view - sys.syscolumns and create a join to the catalog view - sys.types.

The sys.columns view returns a recordset containing a row for every row.  The sys.types view returns a row for systems and user-defined type.

A query example returning the column name, table name, data type name of a specific table

 

use My_DB
go
select s1.name,OBJECT_NAME(s1.id),t1.name  from sys.syscolumns s1
INNER JOIN sys.types t1 ON s1.xtype = t1.user_type_id 
where OBJECT_NAME(id) = 'myTable'
order by s1.name

 Read More

Calculate sql row size - SQL Server DBA

SQL Server – Format number with commas using money sql data type

SQL Server - Arithmetic overflow error converting expression to data type int and COUNT_BIG


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Find column data types using sys.types and sys.syscolumns


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer