Caution: Only do this if you’re unsure. You can lose data from a table in case it needs to be rebuilt to make the desired changes.
Hi, i hope this tip hel you…
Management Studio Error : “Saving Changes in not permitted.”
The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created
The reason for SSMS to go unresponsive is that when some operations (i.e. adding column, changing datatype of any column, etc) happen, it has to do much more work than the actual operation. Let us now examine an operation where we will add a new column to an already existing table.
When a new column is added SSMS renames the existing table to temp table.
After that, it creates a new table with new specification.
Now, SSMS has two tables: copy of previous table with data and empty table with new structure.
SSMS now starts moving older data from older table to newer table.
If there is any error, it rolls back transaction.
If there is no error, it commits all the previous operations.
This feature can be turned off by going to Menu >> Tools >> Options >> Designers >> Uncheck “Prevent Saving changes that
DECLARE @Search varchar(255)
o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
WHERE m.definition Like '%'+@Search+'%'
ORDER BY 2,1
select t.sp_name, sum(t.lines_of_code) - 1 as lines_ofcode, t.type_desc
select o.name as sp_name,
(len(c.text) - len(replace(c.text, char(10), ''))) as lines_of_code,
case when o.xtype = 'P' then 'Stored Procedure'
when o.xtype in ('FN', 'IF', 'TF') then 'Function'
end as type_desc
from sysobjects o
inner join syscomments c
on c.id = o.id
where o.xtype in ('P', 'FN', 'IF', 'TF')
-- and o.category = 0
and o.name not in ('fn_diagramobjects', 'sp_alterdiagram', 'sp_creatediagram', 'sp_dropdiagram', 'sp_helpdiagramdefinition', 'sp_helpdiagrams', 'sp_renamediagram', 'sp_upgraddiagrams', 'sysdiagrams')
group by t.sp_name, t.type_desc
order by 1