Dynamic SQL to Apply IsNULL on All Columns of a table

Dynamic SQL to Apply IsNULL on All Columns of a table according to data type.





SELECT 
'isnull('+COLUMN_NAME+',' +CASE WHEN  DATA_TYPE ='varchar' THEN ''''')' WHEN  DATA_TYPE ='datetime' THEN 'getdate())' ELSE '0)' END
,*
FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='Property' AND IS_NULLABLE='NO'

Comments

Post a Comment

Popular posts from this blog

To Get All the Names of tables and Databases their data have changed in last 24 Hours And Backup those databases

-- ============================================= -- Author: Muhammad Jawad -- Create date: 2/3/2017 -- Description: To Get All the Names of tables and Databases their data have changed in last 24 Hours And Backup those databases -- ============================================= Alter PROC UP_FindAndBackupAllDatabaseHaveChanges AS     BEGIN ----**************************START: To get all the tables and database names whose data have changed in last 24 hours **************************----         DECLARE @loopStart INT= 1;         DECLARE @looend INT;         IF OBJECT_ID('tempdb..#AllDatabaseTables') IS NOT NULL             BEGIN                 DROP TABLE #AllDatabaseTables;             END;         CREATE TABLE #AllDatabaseTables             ( ...
Read more

Break comma separated into multiple rows in MYSQL

Image
 Break comma-separated string/column/parameter into multiple rows in MYSQL. with recursive     cs_string as ( select 'a,b,c,d' as items),     cs_rowID as ( select 1 as n union select n + 1 from cs_rowID, cs_string         where n <= length(items) - length(replace(items, ',', ''))) select distinct substring_index(substring_index(items, ',', n), ',', -1)  group_name from cs_rowID, cs_string Output:
Read more

mysql global search procedure

DROP PROCEDURE IF EXISTS findAll; DELIMITER $$ CREATE PROCEDURE findAll(IN `search` TEXT) BEGIN     SET SESSION group_concat_max_len := @@max_allowed_packet;     SELECT GROUP_CONCAT(         "SELECT '", c1.TABLE_NAME, "' AS `table`, '", c1.COLUMN_NAME, "' AS `column`, ",         "CONCAT_WS(',', ",               (SELECT GROUP_CONCAT(CONCAT('`', c2.column_name, '`'))               FROM `information_schema`.`columns` c2               WHERE c1.TABLE_SCHEMA=c2.TABLE_SCHEMA                 AND c1.TABLE_NAME=c2.TABLE_NAME                 AND c2.COLUMN_KEY='PRI'),          ") AS pri, ",         "CONVERT(`", c1.COLUMN_NAME, "` USING utf8mb4) COLLATE utf8mb4_general_ci AS value FROM `", ...
Read more