Mustafa EL-Masry Script

reset the identity coulmn


if you want to reset the identity coulmn in any table use The following line resets the Identity value for the table to 0 so that the next record added starts at 1
DBCC CHECKIDENT(‘Table Name’, RESEED, 0)
So i make Curssor to Select all tables with Identity column in any database then make reset to this tables

DECLARE @Table_Name AS NVARCHAR(100)
DECLARE @NewLineChar AS CHAR(2)
SET @NewLineChar = CHAR(13) + CHAR(10)
DECLARE Reset_Identity CURSOR
for
select o.name
from syscolumns c, sysobjects o
where c.status = 128  and c.xtype = 56 and c.colid =1 and c.offset = 2
and o.id = c.id
order by o.name
OPEN Reset_Identity

FETCH NEXT FROM Reset_Identity INTO @Table_Name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
DECLARE @message varchar(100)
SELECT @message =  ‘DBCC CHECKIDENT(”’ + @Table_Name + ”’, RESEED, 0) ‘  + @NewLineChar +’GO’
PRINT @message
END
FETCH NEXT FROM Reset_Identity INTO @Table_Name
END

CLOSE Reset_Identity
DEALLOCATE Reset_Identity
GO

After you run this code on any database take the result becouse this code will genrate the reset identity column code in the database so if you take the result an run it in any database in this case you make reset  to the identity column

Mustafa EL-Masry Encryption Tips

Options for hiding SQL Server code


The WITH ENCRYPTION clause is used to encrypt the definition of SQL Server objects (views, stored procedures, functions ), so that no one may be able to view the definition/code of that specific object. Using the clause while creating an object is fairly easy, but there are some serious points and possible complications.

Denying View Definition permission will hide the object for a specific user and also the user will not be able to see the definition using sp_helptext.

Mustafa EL-Masry Encryption Tips

Transparent data encryption (TDE)


Introduction: Encrypting at the Database Level :

Transparent data encryption (TDE) is a new encryption feature introduced in Microsoft® SQL Server™ 2008. to use encryption in Microsoft SQL Server 2005, the column data type must be changed to varbinary;

Q & A :

What happen if you need to restore or detach/attach an encrypted database on a different server or instance?
The certificate that was used to encrypt the database encryption key must be available. Otherwise, the operation will fail.

TDE :
Transparent data encryption is the new database-level encryption feature introduced in SQL Server 2008.
How to Enable TDE :
To enable TDE, you must have the normal permissions associated with creating a database master key and certificates in the master database. You must also have CONTROL permissions on the user database.
To enable TDE :

1- Create Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘some password’;
2- Create Certificate
CREATE CERTIFICATE tdeCert WITH SUBJECT = ‘TDE Certificate’;
3- Create Database Encryption
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE tdeCert
4- Enable TDE in Database
ALTER DATABASE myDatabase SET ENCRYPTION ON

Finally, let’s find the state of the database encryption.
SELECT db_name(database_id), encryption_state
FROM sys.dm_database_encryption_keys

Mustafa EL-Masry Script, SQl server Administration

Shrink Database Log File


http://platform.twitter.com/widgets/hub.1326407570.html

Shrink Database Log File

Only use this script for SQL Server development servers!
Script must be executed as sysadmin
This script will execute the following actions on all databases

– set recovery model to [Simple]
– trucate log file
– shrink log file
– Set recovery model to Simple

use [master]
go

— Declare container variabels for each column we select in the cursor
declare @databaseName nvarchar(128)

— Define the cursor name
declare databaseCursor cursor
— Define the dataset to loop
for
select [name] from sys.databases where database_id>4

— Start loop
open databaseCursor

— Get information from the first row
fetch next from databaseCursor into @databaseName

— Loop until there are no more rows
while @@fetch_status = 0
begin
print ‘Setting recovery model to Simple for database [‘ + @databaseName + ‘]’
exec(‘alter database [‘ + @databaseName + ‘] set recovery Simple’)

checkpoint

Print ‘checkpoint’

print ‘Shrinking logfile for database [‘ + @databaseName + ‘]’
exec(‘
use [‘ + @databaseName + ‘];’ +’

declare @logfileName nvarchar(128);
set @logfileName = (
select top 1 [name] from sys.database_files where [type] = 1
);
dbcc shrinkfile(@logfileName,1);
‘)
print ‘Setting recovery model to FULL for database [‘ + @databaseName + ‘]’
exec(‘alter database [‘ + @databaseName + ‘] set recovery Full’)
— Get information from next row
fetch next from databaseCursor into @databaseName
end

— End loop and clean up
close databaseCursor
deallocate databaseCursor
go

Mustafa EL-Masry Script, SQl server Administration

Create view By cursor


Create view By cursor

now my manager ask me to make views in database ((A)) from all tables already exist  in database ((B)) that’s hard task to catch tha tabel one by one and make create view XXXX as select * from Table_name . this operation very hard and will take a long time . so my friend MR/Mohamed Osman make Script dynamic to make this Operation .

Run this script on the database (B) where there are tables and the database you want to make the views select from there put the name of this DB in prameter name @DBNAME

— =============================================
— Create View dynamic
— Created by Mohamed osman
— =============================================
DECLARE @DBNAME AS NVARCHAR(100)
DECLARE @NewLineChar AS CHAR(2)
SET @NewLineChar = CHAR(13) + CHAR(10)
SET @DBNAME =’GEN2010.DBO.’ —— views will select from this database
DECLARE @RESULT AS NVARCHAR (MAX)

DECLARE V CURSOR
READ_ONLY
FOR SELECT  ‘Create View ‘ + name + ‘ AS SELECT * FROM  ‘ + @DBNAME +  name
FROM sys.objects AS so WHERE so.type = ‘U’
DECLARE @name varchar(MAX)
OPEN V

FETCH NEXT FROM V INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

DECLARE @message varchar(max)
SELECT @message =  @name+ @NewLineChar +’GO’
PRINT @message
END
FETCH NEXT FROM V INTO @name
END

CLOSE V
DEALLOCATE V
GO