Hello,

I want ask, there is query to retrieve data with data type on table SQL Server 2008 R2 ?

Depending on what you mean - there are a few different things you can do.

You can run this query:  sp_help TableName

It spits out a series of datasets that describe the table including the columns and their datatypes. 

Depending which version of SQL you are using you could also use: 

select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='TableName'

You can also use the SQL_VARIANT_PROPERTY function like below...

 select SQL_VARIANT_PROPERTY(ColumnName,'BaseType') as Type
,SQL_VARIANT_PROPERTY(ColumnName,'Precision') as Precision
,SQL_VARIANT_PROPERTY(ColumnName,'Scale') as Scale
FROM TABLE

Can you give us an idea of what you are trying to achieve? 

EDIT: If you don't need to use this information programatically you can just look in the Object Explorer in SSMS.

EDIT2: See this link also, it has some queries for older versions of SQL Server: http://stackoverflow.com/questions/18298433/how-can-i-show-the-table-structure-in-sql-server-query

Please take a look at  sp_describe_first_result_set,sp_describe_undeclared_parameters, and sys.dm_exec_describe_first_result_set.

• Edited by Saeid Hasani 2 hours 45 minutes ago

select  columns.* 
from    information_schema.columns columns
join    information_schema.tables  tables
on      tables.table_name = columns.table_name
where   tables.table_type = 'base table'
and     columns.data_type = 'decimal'
order by columns.table_name, columns.column_name

Can you explain what you mean by retrieve data with data type ?

DO you mean retrieving data as well as metadata for the table?

Yes, you can use system catalogue views, like sys.columns or INFORMATION_SCHEMA.COLUMNS to get the datatype of table columns, check here: http://sqlwithmanoj.com/2010/12/06/querying-sql-server-metadata/

Also check my blog post on how to get datatype of literal values: http://sqlwithmanoj.com/2011/01/27/sql_variant-datatype/