Category: Script

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

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

Event Can prevent happend on your database
CREATE_TABLE
CREATE_INDEX
CREATE_FUNCTION

Example

USE
DBNAME;
GO
CREATE TRIGGER Prevent_Create

ON
DATABASE FOR CREATE_TABLE
AS
PRINT ‘CREATE TABLE Issued.’
SELECT EVENTDATA().value
(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar(max)’)
RAISERROR (‘New tables cannot be created in this database.’, 16, 1)
ROLLBACK;
GO
–Test the trigger.
CREATE TABLE T2 (C1 int);
GO
–Drop the trigger.
DROP TRIGGER Prevent_Create_Table ON DATABASE;
GO