Amir Mirkamali

امیر میرکمالی

SQL Server 2008 - Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.

When you save a table in Management Studio that requires the table be dropped and recreated behind the scenes, the change will fail by default with the below warning.

In SQL Server, tables are dropped, recreated and reloaded automatically for you without having to worry about what's going on behind the curtains but this may create issues for some users. This behavior is required upon a number of actions but most common is creating a new column in a specific location in the table.

Warning Message:

Saving changes is 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.

To fix this in Management Studio, go to Tools -> Options then go to the Designer Page and uncheck "Prevent saving changes that require table re-creation".

Get Number of rows in each table of database

DECLARE @SQLString nvarchar (255),
@ParmDefinition nvarchar (255)

DECLARE @rowsCount int
DECLARE @tablename sysname, @Empty char (1)

DECLARE FindNONEmptyTables CURSOR READ_ONLY

FOR SELECT TABLE_SCHEMA+'.'+TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN FindNONEmptyTables

FETCH NEXT FROM FindNONEmptyTables INTO @tablename
WHILE (@@fetch_status = 0)
BEGIN

SET @SQLString = N'
SELECT @rowsCount= COUNT(*) FROM ' + @tablename +
'; IF EXISTS (SELECT * FROM ' + @tablename + ') set
@Empty = ''N'' ELSE set @Empty = ''Y'''
SET @ParmDefinition = N'@tablename sysname, @Empty char(1) OUTPUT, @rowsCount int OUTPUT'

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@tablename = @tablename,
@Empty = @Empty OUTPUT,
@rowsCount = @rowsCount OUTPUT

IF @Empty = 'N'
BEGIN
 PRINT @tablename + ' rows count is :' + CAST (@rowsCount AS NVARCHAR(50))
END
FETCH NEXT FROM FindNONEmptyTables INTO @tablename
END

CLOSE FindNONEmptyTables
DEALLOCATE FindNONEmptyTables
GO