How to handle the Inserts which are not having column list in case of a New column added
Suppose There is a Table Called List its has 2 columns Id and Name There are many sp which are having code as Insert Into List Values(@Id,@Name) If I add a new column say Sequence all the Old sp will start giving error column name or number of supplied values does not match table definition Is there any way we can handle this without changing all the Sp I tried Default but it didn't worked out
You'll need to edit those procedures. There's no other way around it. I wouldn't recommend writing the code that way in the first place. Enumerate the columns that you're inserting into as well as the values. At least in that case, if the new column was nullable or had a default value, the procedures would continue to work.
This is why you shouldn't do inserts without a column list. I'd say you have to rewrite your stored procedures. A workaround if the number of procedures is massive and you can't get around to doing it just now would perhaps be to rename the table and create a view which does a SELECT id, name FROM "new table name" But I wouldn't really recommend doing that, you most probably add a column to the table because it is meaningful in some way. And your other queries which needs the new column from the table would have to be changed to use the new name of the table, and that way you end up with code which uses two different names for essentially the same object. If the looks of your stored procedures are pretty consistent, you could create a C# program which uses SMO-objects to get the text from all stored procedures and have a RegEx object change your inserts to explicitly use a column list instead of just insert into "tablename" values(...). That requires some knowledge about C# and regular expressions, but you'll probably be able to google for it. If you don't want to have SMO actually alter the procedures, you can set SqlExecutionModes property of the ConnectionContext object to SqlExecutionModes.CaptureSql. That way, your sp.Alter() method calls won't actually alter the procecure, instead you can find the DDL in a string Collection in ConnectionContext.CapturedSql.Text. Just loop through it and write the string values to a text file which you can Review Before executing it in Management Studio.