CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Nits Patel
Nits Patel

Reputation: 390

Get column data type from table and some condition

I want column data type and if data type is int then show column name and if varchar then show column name with '' in horizontal line alien

I tried something like but don't know how to apply condition: 

SELECT 
    t.Name 'Data type'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
    where object_id = OBJECT_ID('tbl_Check_Sql_Query')

Suppose my column has Data type 

id int 
name varchar 
Email_ID varchar

Then i want 

id,'name','Email_ID'

Upvotes: 2

Views: 319

Answers (1)

Ilyes
Ilyes

Reputation: 14928

You can directly use INFORMATION_SCHEMA.COLUMNS system view as

SELECT TABLE_NAME,
       CASE DATA_TYPE WHEN 'INT' THEN COLUMN_NAME
                      --WHEN 'VARCHAR' I don't know what you mean by "in horizontal line alien"
       END
FROM INFORMATION_SCHEMA.COLUMNS;
--WHERE TABLE_NAME = 'TableName'

Or

SELECT TABLE_NAME,
       STRING_AGG(CASE WHEN DATA_TYPE = 'VARCHAR'
                       THEN QUOTENAME(COLUMN_NAME, '''')
                       ELSE COLUMN_NAME
                  END, ',') Columns
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME;
--WHERE TABLE_NAME = 'TableName'

Demo

Or 

SELECT TABLE_NAME,
       STUFF(
         (SELECT ',' + CASE WHEN DATA_TYPE = 'VARCHAR' 
                            THEN QUOTENAME(COLUMN_NAME, '''')
                            ELSE COLUMN_NAME
                       END
          FROM INFORMATION_SCHEMA.COLUMNS T2
          WHERE T1.TABLE_NAME = T2.TABLE_NAME
          FOR XML PATH('')

         ), 1, 1, ''
       ) Columns
FROM INFORMATION_SCHEMA.COLUMNS T1
GROUP BY TABLE_NAME;

2nd Demo

UPDATE:

Since you're trying to return GETDATE() value when the data type is DATETIME then

SELECT TABLE_NAME,
       STUFF(
         (SELECT ',' + CASE DATA_TYPE WHEN 'VARCHAR' 
                                          THEN QUOTENAME(COLUMN_NAME, '''')
                                      WHEN 'DATETIME'
                                          THEN 'GETDATE()'
                            ELSE COLUMN_NAME
                       END
          FROM INFORMATION_SCHEMA.COLUMNS T2
          WHERE T1.TABLE_NAME = T2.TABLE_NAME
          FOR XML PATH('')

         ), 1, 1, ''
       ) Columns
FROM INFORMATION_SCHEMA.COLUMNS T1
GROUP BY TABLE_NAME;

Upvotes: 4

Related Questions