Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Identify Column Data Types Expand / Collapse
Author
Message
Posted Saturday, March 22, 2014 1:13 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 1:57 PM
Points: 485, Visits: 1,374
Comments posted to this topic are about the item Identify Column Data Types
Post #1553738
Posted Monday, April 7, 2014 8:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 1:57 AM
Points: 1, Visits: 2
My favorites to retrieve column information

-- Find nullable columns
select TABLE_NAME, COLUMN_NAME, DATA_TYPE from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME in (select name from sysobjects where type='U')
and IS_NULLABLE = 'YES'
and Left(TABLE_NAME, 3) NOT IN ('sys')
order by TABLE_NAME;

-- Find column with collation name set
select TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_SET_NAME, COLLATION_NAME from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME in (select name from sysobjects where type='U')
and ((CHARACTER_SET_NAME is not null) or (COLLATION_NAME is not null))
order by TABLE_NAME;

-- Find different datatypes
select DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, DATETIME_PRECISION, count(*) as "Count" from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME in (select name from sysobjects where type='U' and Left(name, 3) NOT IN ('sys'))
group by DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, DATETIME_PRECISION
order by DATA_TYPE;

/Jonas
Post #1559113
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse