Just a quicky.

Lets say that someone provided you with a database, but that database had the wrong methodology applied for date time.  In this case, they set the data type for all tables that needed to capture dates, to datetime.  But, then they decided that they really wanted them to be the data type of date.

So, here is some code to get into the schema, and generate alter statements to convert all datetime type columns to date type columns.



SELECT DISTINCT 'ALTER TABLE [' + sch.name + '].[' + so.name 
                + '] ALTER COLUMN [' + sc.name + '] date' 
--select * 
FROM   sys.objects so 
       INNER JOIN sys.schemas sch 
               ON so.schema_id = sch.schema_id 
       JOIN sys.columns sc 
         ON so.object_id = sc.object_id 
            AND so.type = 'U' 
            AND sc.system_type_id = 61 
            AND sc.user_type_id = 61

You could use the above code, if you wanted, with whatever user type you like, and change the code for concatonating the data as well.