Generate SQL-code with line breaks from metadata
I’m updating a system with lots of tables and i wanted to perform the same operation on more than 20 of the tables. Writing all those update statements by hand is errorprone and using copy-paste is equally worse.
I wanted to write good code with linebreaks, logmessages and comments. What to do?
Code-generation to the rescue!
1 -- Find tables with columnname personid 2 select 3 ','''+ sysobj.name + '''', syscol.name '.'''+ syscol.name + '''' 4 5 -- Code for updating the column. 6 ,' 7 IF EXISTS (SELECT 1 FROM SAMLEDB..'+ sysobj.name + ' WHERE SAMLEDB..'+ sysobj.name + '.'+ syscol.name + ' < 10000) 8 BEGIN 9 UPDATE SAMLEDB..'+ sysobj.name + ' 10 SET SAMLEDB..'+ sysobj.name + '.'+ syscol.name + ' = (SAMLEDB..'+ sysobj.name + '.'+ syscol.name + ' + 10000) 11 FROM SAMLEDB..'+ sysobj.name + ' 12 WHERE SAMLEDB..'+ sysobj.name + '.'+ syscol.name + ' < 10000 13 END 14 ' AS UpdateText 15 from sysobjects sysobj, syscolumns syscol 16 where 17 sysobj.id = syscol.id 18 and syscol.name like 'personid' 19 order by syscol.name ,sysobj.name
Which will generate output like:
1 IF EXISTS (SELECT 1 FROM SAMLEDB..PERSON WHERE SAMLEDB..PERSON.personid < 10000) 2 BEGIN 3 UPDATE SAMLEDB..PERSON 4 SET SAMLEDB..PERSON.personid = (SAMLEDB..PERSON.personid + 10000) 5 FROM SAMLEDB..PERSON 6 WHERE SAMLEDB..PERSON.personid < 10000 7 END
Problem solved! I know that the names are correct. I treat the tables in a uniform way.
