none
Determine datatype in T-SQL and changing column values

    Question

  • Hi Guys,


    I have this requirement and need your help.

    I hava a table with different data types like varchar  ,numeric etc..

    I need to run a query to determine the datatype of a column and based on that value I need to update that column with  0 or to UPPER CASE.
    i.e if it is a numeric field  and it is null then update that field with 0.
    If it is a varchar field and it is not null  then update that field to UPPER Case.

    Is it possible doing this with T-SQL or  running a SQL Query ?

    Thanks,
    Ram

    Friday, September 25, 2009 8:08 PM

Answers

  • Ram - the following nested cursor loops T-SQL script will visit all the columns (datatype in IN list) and all the tables.

    In the inner loop (where you see the comment), you have to insert your processing code, probably a dynamic SQL UPDATE.

    USE AdventureWorks2008; 
    
    DECLARE  @TableName   NVARCHAR(256), 
             @TableColumn NVARCHAR(128), 
             @DataType    NVARCHAR(128) 
    
    DECLARE curTables CURSOR  FOR 
    SELECT   QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) AS ST 
    FROM     INFORMATION_SCHEMA.TABLES 
    WHERE    TABLE_TYPE = 'BASE TABLE' 
             AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + 
                 QUOTENAME(TABLE_NAME)), 
                                'IsMSShipped') != 1 
    ORDER BY ST 
    
    OPEN curTables 
    
    FETCH NEXT FROM curTables 
    INTO @TableName 
    
    WHILE (@@FETCH_STATUS = 0) -- Outer cursor loop 
      BEGIN 
        PRINT @TableName 
         
        SET @TableColumn = '' 
         
        DECLARE curColumns CURSOR  FOR -- Nested cursor 
         
        SELECT   QUOTENAME(COLUMN_NAME), 
                 DATA_TYPE 
        FROM     INFORMATION_SCHEMA.COLUMNS 
        WHERE    TABLE_NAME = PARSENAME(@TableName,1) 
                 AND TABLE_SCHEMA = PARSENAME(@TableName,2) 
                 AND DATA_TYPE IN ('int','money','numeric','varchar', 
                                   'nvarchar','char','nchar','xml') 
        ORDER BY ORDINAL_POSITION 
         
        OPEN curColumns 
         
        FETCH NEXT FROM curColumns 
        INTO @TableColumn, 
             @DataType 
         
        WHILE (@@FETCH_STATUS = 0) -- Inner cursor loop (nested cursor while) 
          BEGIN 
            PRINT '  ' + @TableColumn + '  ' + @DataType 
             
    /************************************************************** 
    * Your code here - example dynamic SQL to perform UPDATE 
    **************************************************************/ 
    
            FETCH NEXT FROM curColumns 
            INTO @TableColumn, 
                 @DataType 
          END  -- Inner cursor loop 
         
        CLOSE curColumns 
         
        DEALLOCATE curColumns 
         
        FETCH NEXT FROM curTables 
        INTO @TableName 
      END  -- Outer cursor loop 
    
    CLOSE curTables 
    
    DEALLOCATE curTables 
    
    /* Partial output in Messages
    
    [Sales].[SalesTerritory]
      [TerritoryID]  int
      [Name]  nvarchar
      [CountryRegionCode]  nvarchar
      [Group]  nvarchar
      [SalesYTD]  money
      [SalesLastYear]  money
      [CostYTD]  money
      [CostLastYear]  money
    [Sales].[SalesTerritoryHistory]
      [BusinessEntityID]  int
      [TerritoryID]  int
    [Sales].[ShoppingCartItem]
      [ShoppingCartItemID]  int
      [ShoppingCartID]  nvarchar
      [Quantity]  int
      [ProductID]  int
    [Sales].[SpecialOffer]
      [SpecialOfferID]  int
      [Description]  nvarchar
      [Type]  nvarchar
      [Category]  nvarchar
      [MinQty]  int
      [MaxQty]  int
    [Sales].[SpecialOfferProduct]
      [SpecialOfferID]  int
      [ProductID]  int
    [Sales].[Store]
      [BusinessEntityID]  int
      [Name]  nvarchar
      [SalesPersonID]  int
      [Demographics]  xml
    */

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Friday, October 02, 2009 8:08 PM
  • Ram,

    I did not account for Numeric columns because the original requirement is to make the data uppercase.  You will need to add Numeric, Int, Decimal etc... to the where clause filters.

    DECLARE @Table VARCHAR(100),
    		@Cols VARCHAR(8000),
    		@sql NVARCHAR(2000)
    		
    DECLARE cursorDB_Action CURSOR FAST_FORWARD FOR
    SELECT DISTINCT Table_Name
    FROM information_schema.columns
    WHERE data_type IN('VARCHAR','CHAR','NCHAR,','NVARCHAR','NUMERIC','DECIMAL','INT','BIGINT','SMALLINT','TINYINT')
    
    OPEN cursorDB_Action
    FETCH NEXT FROM cursorDB_Action INTO @Table
    
    WHILE @@FETCH_STATUS = 0
        BEGIN
            		
            SET @Cols = NULL;
            
    		SELECT @Cols = COALESCE(@Cols + ' , ','') 
    			+ QUOTENAME(Column_Name) 
    			+ CASE WHEN data_type LIKE '%CHAR%'
    				THEN '=UPPER(' + QUOTENAME(Column_Name) + ')' + CHAR(13)
    			  ELSE '=COALESCE(' + QUOTENAME(Column_Name) + ',0)' + CHAR(13)
    			  END
    		FROM Information_Schema.Columns
    		WHERE data_type IN('VARCHAR','CHAR','NCHAR,','NVARCHAR','NUMERIC','DECIMAL','INT','BIGINT','SMALLINT','TINYINT')
    			AND table_Name = @Table
        
    		SET @sql = 
    			N'UPDATE ' + @Table + '	SET ' + @Cols + CHAR(13)
    			
    		PRINT @sql
    		--EXEC SP_EXECUTESQL @sql
    		
    		FETCH NEXT FROM cursorDB_Action INTO @Table
        END
    
    CLOSE cursorDB_Action
    DEALLOCATE cursorDB_Action
    

    http://jahaines.blogspot.com/
    • Marked as answer by ram001 Friday, October 02, 2009 10:38 PM
    Friday, October 02, 2009 10:17 PM

All replies

  • This code will show you the names and data types of the columns

    SELECT c.name AS col_name,
           TYPE_NAME(c.user_type_id) AS type_name
    FROM sys.objects AS o 
    JOIN sys.columns AS c  ON o.object_id = c.object_id
    WHERE o.name = 'TableName'
    
    

    Abdallah, PMP, MCTS
    • Edited by Abdshall Saturday, September 26, 2009 12:41 AM
    Friday, September 25, 2009 8:19 PM
  • Your code does the retrieving the column name and type but missing datatype comparison.

    How can I determine whether it a numeric or varchar field?

    Is there IF ELSE construct in T-SQL?

    • Proposed as answer by Abdshall Friday, September 25, 2009 8:47 PM
    • Unproposed as answer by Adam HainesModerator Friday, October 02, 2009 7:36 PM
    Friday, September 25, 2009 8:36 PM
  • I'm sorry, I pasted the wrong code before editing.I will repost the right code.


    Abdallah, PMP, MCTS
    Friday, September 25, 2009 8:45 PM
  • You can use the coe below.
    You can basically name your table name(Employees), Column name you want to check on(ManagerID), and the see if it's an integer.

    UPDATE Employees SET ManagerID = CASE
    WHEN ((SELECT TYPE_NAME(c.user_type_id)
    		FROM sys.objects AS o 
    			JOIN sys.columns AS c  ON o.object_id = c.object_id
    		WHERE o.name = 'Employees' and c.name ='ManagerID') = 'int'
    		AND mgrid IS NULL) THEN 0  
    ELSE ManagerID
    END
    

    Abdallah, PMP, MCTS
    • Marked as answer by ram001 Monday, September 28, 2009 9:26 PM
    • Unmarked as answer by ram001 Friday, October 02, 2009 7:25 PM
    Friday, September 25, 2009 10:32 PM
  • Your code does the retrieving the column name and type but missing datatype comparison.

    How can I determine whether it a numeric or varchar field?

    Is there IF ELSE construct in T-SQL?


    Propsed As Answer by mistake
    Abdallah, PMP, MCTS
    Saturday, September 26, 2009 12:42 AM
  • I have more than 50 tables.

    I don't want to specify the table name and or atleast column names.
    It has to do this for all the columns in a table.

    Please help me how to go about it.?

    I really appreciate your help

    Friday, October 02, 2009 7:15 PM
  • Your code does the retrieving the column name and type but missing datatype comparison.

    How can I determine whether it a numeric or varchar field?

    Is there IF ELSE construct in T-SQL?


    Propsed As Answer by mistake
    Abdallah, PMP, MCTS

    i think there should be some way to unpropose the answer.
    Friday, October 02, 2009 7:27 PM
  • Your code does the retrieving the column name and type but missing datatype comparison.

    How can I determine whether it a numeric or varchar field?

    Is there IF ELSE construct in T-SQL?


    Propsed As Answer by mistake
    Abdallah, PMP, MCTS

    i think there should be some way to unpropose the answer.
    You are right Arif, there should. I was kinda of nervous to know that I couldn't unpropose.

    Abdallah, PMP, MCTS
    Friday, October 02, 2009 7:38 PM
  • Your code does the retrieving the column name and type but missing datatype comparison.

    How can I determine whether it a numeric or varchar field?

    Is there IF ELSE construct in T-SQL?


    Propsed As Answer by mistake
    Abdallah, PMP, MCTS

    i think there should be some way to unpropose the answer.
    Arif,

    You dont see the un propose as answer button?  If not this functionality, may be limited to "Answerers" and "Moderators", although I believe that if you can mark it you should be able to unmark it.

    Let me know.


    http://jahaines.blogspot.com/
    • Proposed as answer by Arif Hasan Friday, October 02, 2009 9:06 PM
    Friday, October 02, 2009 7:38 PM
  • Ram - the following nested cursor loops T-SQL script will visit all the columns (datatype in IN list) and all the tables.

    In the inner loop (where you see the comment), you have to insert your processing code, probably a dynamic SQL UPDATE.

    USE AdventureWorks2008; 
    
    DECLARE  @TableName   NVARCHAR(256), 
             @TableColumn NVARCHAR(128), 
             @DataType    NVARCHAR(128) 
    
    DECLARE curTables CURSOR  FOR 
    SELECT   QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) AS ST 
    FROM     INFORMATION_SCHEMA.TABLES 
    WHERE    TABLE_TYPE = 'BASE TABLE' 
             AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + 
                 QUOTENAME(TABLE_NAME)), 
                                'IsMSShipped') != 1 
    ORDER BY ST 
    
    OPEN curTables 
    
    FETCH NEXT FROM curTables 
    INTO @TableName 
    
    WHILE (@@FETCH_STATUS = 0) -- Outer cursor loop 
      BEGIN 
        PRINT @TableName 
         
        SET @TableColumn = '' 
         
        DECLARE curColumns CURSOR  FOR -- Nested cursor 
         
        SELECT   QUOTENAME(COLUMN_NAME), 
                 DATA_TYPE 
        FROM     INFORMATION_SCHEMA.COLUMNS 
        WHERE    TABLE_NAME = PARSENAME(@TableName,1) 
                 AND TABLE_SCHEMA = PARSENAME(@TableName,2) 
                 AND DATA_TYPE IN ('int','money','numeric','varchar', 
                                   'nvarchar','char','nchar','xml') 
        ORDER BY ORDINAL_POSITION 
         
        OPEN curColumns 
         
        FETCH NEXT FROM curColumns 
        INTO @TableColumn, 
             @DataType 
         
        WHILE (@@FETCH_STATUS = 0) -- Inner cursor loop (nested cursor while) 
          BEGIN 
            PRINT '  ' + @TableColumn + '  ' + @DataType 
             
    /************************************************************** 
    * Your code here - example dynamic SQL to perform UPDATE 
    **************************************************************/ 
    
            FETCH NEXT FROM curColumns 
            INTO @TableColumn, 
                 @DataType 
          END  -- Inner cursor loop 
         
        CLOSE curColumns 
         
        DEALLOCATE curColumns 
         
        FETCH NEXT FROM curTables 
        INTO @TableName 
      END  -- Outer cursor loop 
    
    CLOSE curTables 
    
    DEALLOCATE curTables 
    
    /* Partial output in Messages
    
    [Sales].[SalesTerritory]
      [TerritoryID]  int
      [Name]  nvarchar
      [CountryRegionCode]  nvarchar
      [Group]  nvarchar
      [SalesYTD]  money
      [SalesLastYear]  money
      [CostYTD]  money
      [CostLastYear]  money
    [Sales].[SalesTerritoryHistory]
      [BusinessEntityID]  int
      [TerritoryID]  int
    [Sales].[ShoppingCartItem]
      [ShoppingCartItemID]  int
      [ShoppingCartID]  nvarchar
      [Quantity]  int
      [ProductID]  int
    [Sales].[SpecialOffer]
      [SpecialOfferID]  int
      [Description]  nvarchar
      [Type]  nvarchar
      [Category]  nvarchar
      [MinQty]  int
      [MaxQty]  int
    [Sales].[SpecialOfferProduct]
      [SpecialOfferID]  int
      [ProductID]  int
    [Sales].[Store]
      [BusinessEntityID]  int
      [Name]  nvarchar
      [SalesPersonID]  int
      [Demographics]  xml
    */

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Friday, October 02, 2009 8:08 PM
  • Hi , Your code works great and thank you . But, I am struggling to update columns to uppercase. I would appreciate if you can help me on this. Thanks, Ram
    Friday, October 02, 2009 8:38 PM
  • Here is what I have so far. This code is not tested as I'm extremely busy at work (I know, it's only Friday). Please take a look at it and test it. Let me know if you have any issues, and I will take another look if I finish work early


    --These are your variables and temp tables
    DECLARE @ColCount int,@j INT
    DECLARE @row int, @ColName varchar(50),@ColType varchar(50),@ColName varchar(50),@i int
    ,@SQLString nvarchar(max),@TableName varchar(100)
    
    --Table to hold your Tables
    CREATE TABLE #Tables (ID int IDENTITY(1,1),Tab varchar(100))
    --Table to hold your columns
    CREATE table #temp (id int identity(1,1),ColName varchar(50),ColType varchar(max)
    
    SELECT @ColCount = 0 , @j = 1
    --Get all the table names in your database
    INSERT INTO #Tables (Tab)
    SELECT table_name from dwa.information_schema.tables
    --Get the count of the tables and then loop through each one of them to get it's columns
    SELECT @ColCount = @@rowcount
    WHILE @j <= @ColCount
    	BEGIN
    		SELECT @TableName = Tab FROM #Tables WHERE ID = @j
    
    		INSERT INTO #temp (ColName,ColType)
    		SELECT column_name,data_type from dwa.information_schema.columns 
    		WHERE Table_Name = @TableName
    		SET @row = @@rowcount
    		SET @i = 1
    		WHILE @i <= @row
    			BEGIN
    				SELECT @ColName = ColName,@ColType = ColType
    				FROM #Temp WHERE ID = @i
    
    				SET @SQLString = 'UPDATE ' + @TableName + ' SET ' + @ColName + ' = CASE
    				WHEN ((SELECT TYPE_NAME(c.user_type_id)
    				FROM sys.objects AS o 
    				JOIN sys.columns AS c  ON o.object_id = c.object_id
    				WHERE o.name = ' + @TableName + ' and c.name = ' + @ColName + ') = ''int''
    				AND ' + @ColName + ' IS NULL) THEN 0  
    				ELSE UPPER(' + @Colname + '
    				END'
    				EXECUTE sp_ExecuteSQL @statement = @SQLString
    				SET @i = @i + 1
    			END
    		SET @j = @j + 1
    	END
    

    Abdallah, PMP, MCTS
    Friday, October 02, 2009 8:46 PM
  • This code where Kalman has the comments listed should work:

    declare @sql nvarchar(500)
    set @sql = 'update ' + @TableName + ' set ' + @TableColumn + ' = UPPER(' + @TableColumn + ')'
    EXEC sp_executesql @sql

    All you have to do is add an IF statement to test for your numeric columns versus your character columns.


    Phil Brammer | http://www.ssistalk.com
    Friday, October 02, 2009 8:50 PM
  • Here is a very simple approach.  Note: I have commented out the exec statement.  You will need to uncomment it out, but I recommend you look at the print statements before executing.

    DECLARE @Table VARCHAR(100),
    		@Cols VARCHAR(8000),
    		@sql NVARCHAR(2000)
    		
    DECLARE cursorDB_Action CURSOR FAST_FORWARD FOR
    SELECT DISTINCT Table_Name
    FROM information_schema.columns
    WHERE data_type IN('VARCHAR','CHAR','NCHAR,','NVARCHAR')
    
    OPEN cursorDB_Action
    FETCH NEXT FROM cursorDB_Action INTO @Table
    
    WHILE @@FETCH_STATUS = 0
        BEGIN
            		
            SET @Cols = NULL;
            
    		SELECT @Cols = COALESCE(@Cols + ' , ','') 
    			+ QUOTENAME(Column_Name) 
    			+ '=UPPER(' + QUOTENAME(Column_Name) + ')' + CHAR(13)
    		FROM Information_Schema.Columns
    		WHERE data_type IN('VARCHAR','CHAR','NCHAR','NVARCHAR')
    			AND table_Name = @Table
        
    		SET @sql = 
    			N'UPDATE ' + @Table + '	SET ' + @Cols + CHAR(13)
    			
    		PRINT @sql
    		--EXEC SP_EXECUTESQL @sql
    		
    		FETCH NEXT FROM cursorDB_Action INTO @Table
        END
    
    CLOSE cursorDB_Action
    DEALLOCATE cursorDB_Action
    

    http://jahaines.blogspot.com/
    Friday, October 02, 2009 9:01 PM
  • No.It's not working.
    Friday, October 02, 2009 9:02 PM
  • Your code does the retrieving the column name and type but missing datatype comparison.

    How can I determine whether it a numeric or varchar field?

    Is there IF ELSE construct in T-SQL?


    Propsed As Answer by mistake
    Abdallah, PMP, MCTS

    i think there should be some way to unpropose the answer.
    Arif,

    You dont see the un propose as answer button?  If not this functionality, may be limited to "Answerers" and "Moderators", although I believe that if you can mark it you should be able to unmark it.

    Let me know.


    http://jahaines.blogspot.com/

    Hi Adam,

    thanks for your response. Just for the verification I have marked your last post as "Porpose As Answer" and now i dont have any button to unpropose it. Please unpropose it by yourself :)



    Friday, October 02, 2009 9:10 PM
  • Hi Adam Haines

    Your code works great.
    But I am having a problem when I tried to include this code

    SELECT @Cols = COALESCE(@Cols + ' , ','')

    + QUOTENAME(Column_Name)

    + '=0' + CHAR(13) + DATA_TYPE

    FROM Information_Schema.Columns

    WHERE data_type IN('INT','NUMERIC','int','numeric')

    AND table_Name = @Table

    SET @sql =

    N'UPDATE ' + @Table + ' SET ' + @Cols + CHAR(13)

    PRINT @sql
    ------------------

    I want to update an numeric column to 0 if it is null.

    Appreciate your help.

    Thanks,
    Ram

    Friday, October 02, 2009 10:06 PM
  • Ram,

    I did not account for Numeric columns because the original requirement is to make the data uppercase.  You will need to add Numeric, Int, Decimal etc... to the where clause filters.

    DECLARE @Table VARCHAR(100),
    		@Cols VARCHAR(8000),
    		@sql NVARCHAR(2000)
    		
    DECLARE cursorDB_Action CURSOR FAST_FORWARD FOR
    SELECT DISTINCT Table_Name
    FROM information_schema.columns
    WHERE data_type IN('VARCHAR','CHAR','NCHAR,','NVARCHAR','NUMERIC','DECIMAL','INT','BIGINT','SMALLINT','TINYINT')
    
    OPEN cursorDB_Action
    FETCH NEXT FROM cursorDB_Action INTO @Table
    
    WHILE @@FETCH_STATUS = 0
        BEGIN
            		
            SET @Cols = NULL;
            
    		SELECT @Cols = COALESCE(@Cols + ' , ','') 
    			+ QUOTENAME(Column_Name) 
    			+ CASE WHEN data_type LIKE '%CHAR%'
    				THEN '=UPPER(' + QUOTENAME(Column_Name) + ')' + CHAR(13)
    			  ELSE '=COALESCE(' + QUOTENAME(Column_Name) + ',0)' + CHAR(13)
    			  END
    		FROM Information_Schema.Columns
    		WHERE data_type IN('VARCHAR','CHAR','NCHAR,','NVARCHAR','NUMERIC','DECIMAL','INT','BIGINT','SMALLINT','TINYINT')
    			AND table_Name = @Table
        
    		SET @sql = 
    			N'UPDATE ' + @Table + '	SET ' + @Cols + CHAR(13)
    			
    		PRINT @sql
    		--EXEC SP_EXECUTESQL @sql
    		
    		FETCH NEXT FROM cursorDB_Action INTO @Table
        END
    
    CLOSE cursorDB_Action
    DEALLOCATE cursorDB_Action
    

    http://jahaines.blogspot.com/
    • Marked as answer by ram001 Friday, October 02, 2009 10:38 PM
    Friday, October 02, 2009 10:17 PM
  • Works great. Thanks
    Friday, October 02, 2009 10:38 PM