SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Identify Column Data Types

By Rich Yarger,

--The following offers a result set for just the Database, Schema, Table Name, Column Name, Data Type, and Maximum Character Length from information_schema.columns.
select TABLE_CATALOG AS 'Database', TABLE_SCHEMA AS 'Schema', TABLE_NAME AS 'Table Name', COLUMN_NAME AS 'Column Name', DATA_TYPE AS 'Data Type', CHARACTER_MAXIMUM_LENGTH AS 'Max Length'
from information_schema.columns
WHERE DATA_TYPE = 'ntext'
--Will provide a count of 'text type' data type columns.
select COUNT (DATA_TYPE)
from information_schema.columns
WHERE DATA_TYPE = 'nvarchar'
OR DATA_TYPE = 'varchar'
OR DATA_TYPE = 'nchar'
OR DATA_TYPE = 'char'
OR DATA_TYPE = 'text'
OR DATA_TYPE = 'ntext'
--Full query, and just a good place to start.
select COUNT (*)
from information_schema.columns

Total article views: 1210 | Views in the last 30 days: 1
 
Related Articles
FORUM

Retrieve column headers for INFORMATION_SCHEMA

Get INFORMATION_SCHEMA column headers

ARTICLE

Problem Solving With Information Schema Columns

Directly accessing system tables is discouraged - so what's the alternative? Using the Information S...

ARTICLE

Querying the INFORMATION_SCHEMA

Using a macro to query the INFORMATION_SCHEMA

FORUM

INFORMATION_SCHEMA

How to hide INFORMATION_SCHEMA from group of uers.

FORUM

Get longest length of data in a column

Get longest length of data in a column

Tags
columns    
datatypes    
 
Contribute