Mustafa EL-Masry SQl server Administration

An Introduction to SQL Server FileStream


Introduction

File Stram is new Feature in sql server 2008 This Feature Used To storage and  management of unstructured data  (example: word documents, image files, music and videos ).

FILESTREAM is not a data type.

Enable FilsStraem

  1. Enable  FILESTREAM for Transact-SQL access
  2. Enable FILESTREAM for file I/O streaming access
  3. Allow remote clients to have streaming access to FILESTREAM data
  4. To make this Open SQL Server configraution under sql services select te instance name you want to enable file stream then right click on this instance then select peroperites then filestream tab .
  5. After FileStream Enable open sql server query then ruen this script to Configure Filestream

EXEC

sp_configurefilestream_access_level, 2

GO

RECONFIGURE

GO

—————————————————————————

CREATE DATABASE NorthPole
ON
PRIMARY (
NAME = NorthPoleDB,
FILENAME = ‘E:\Temp\NP\NorthPoleDB.mdf’
), FILEGROUP NorthPoleFS CONTAINS FILESTREAM(
NAME = NorthPoleFS,
FILENAME = ‘E:\Temp\NP\NorthPoleFS’)
LOG ON (
NAME = NorthPoleLOG,
FILENAME = ‘E:\Temp\NP\NorthPoleLOG.ldf’)
GO
—————————————————-
use NorthPole
go
CREATE TABLE [dbo].[Items](
[ItemID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
[ItemNumber] VARCHAR(20),
[ItemDescription] VARCHAR(50),
[ItemImage] VARBINARY(MAX) FILESTREAM NULL
)
———————————————————-
— Declare a variable to store the image data
DECLARE @img AS VARBINARY(MAX)

— Load the image data
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(
BULK
‘E:\Temp\NP\MicrosoftMouse.jpg’,
SINGLE_BLOB ) AS x
select @img
— Insert the data to the table
INSERT INTO Items (ItemID, ItemNumber, ItemDescription, ItemImage)
SELECT NEWID(), ‘MS1001′,’Microsoft Mouse’, @img
——————————————————————-
–Example No 2
CREATE TABLE dbo.PictureTable
(
PkId int Primary Key IDENTITY (1, 1),
Id uniqueidentifier NOT NULL Unique ROWGUIDCOL Default newid(),
Description nvarchar(64) NOT NULL,
FileSummary varbinary(MAX),
FileData varbinary(MAX) FileStream NULL
)
——————————————————————
Insert Into PictureTable([Description],[FileData])
Values(‘Hello World’, Cast(‘Hello World’ As varbinary(max)))
–And then select using the statement
SELECT [PkId],[Id],[Description],[FileData],CAST([FileData] As varchar(Max)) FROM [PictureTable]
————————————————————————————————-

References

http://www.simple-talk.com/sql/learn-sql-server/an-introduction-to-sql-server-filestream/

 

Mustafa EL-Masry SQl server Administration

Remove all data from the database


SET NOCOUNT ON
DECLARE @IgnoreTables TABLE (TableName varchar(512))
INSERT INTO @IgnoreTables (TableName) VALUES (‘sysdiagrams’)
DECLARE @AllRelationships TABLE (ForeignKey varchar(512),TableName varchar(512),ColumnName varchar(512),
 ReferenceTableName varchar(512),ReferenceColumnName varchar(512),DeleteRule varchar(512))
INSERT INTO @AllRelationships
 SELECT f.name AS ForeignKey,
 OBJECT_NAME(f.parent_object_id) AS TableName,
 COL_NAME(fc.parent_object_id,
 fc.parent_column_id) AS ColumnName,
 OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
 COL_NAME(fc.referenced_object_id,
 fc.referenced_column_id) AS ReferenceColumnName,
 delete_referential_action_desc as DeleteRule
 FROM sys.foreign_keys AS f
 INNER JOIN sys.foreign_key_columns AS fc
 ON f.OBJECT_ID = fc.constraint_object_id
 

DECLARE @TableOwner varchar(512)
DECLARE @TableName varchar(512)
DECLARE @ForeignKey varchar(512)
DECLARE @ColumnName varchar(512)
DECLARE @ReferenceTableName varchar(512)
DECLARE @ReferenceColumnName varchar(512)
DECLARE @DeleteRule varchar(512)

 
 
 
PRINT(‘Loop through all tables and switch all constraints to have a delete rule of CASCADE’)
DECLARE DataBaseTables0 CURSOR FOR
SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name
FROM sys.tables AS t;

OPEN DataBaseTables0;

FETCH NEXT FROM DataBaseTables0
INTO @TableOwner,@TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
 
 IF (NOT EXISTS(SELECT TOP 1 1 FROM @IgnoreTables WHERE TableName = @TableName))
 BEGIN
 PRINT ‘[‘+@TableOwner+’].[‘ + @TableName + ‘]’;
 
 DECLARE DataBaseTableRelationships CURSOR FOR
 SELECT ForeignKey, ColumnName, ReferenceTableName, ReferenceColumnName
 FROM @AllRelationships
 WHERE TableName = @TableName

 OPEN DataBaseTableRelationships;
 FETCH NEXT FROM DataBaseTableRelationships INTO @ForeignKey, @ColumnName, @ReferenceTableName, @ReferenceColumnName;

 IF @@FETCH_STATUS <> 0
 PRINT ‘=====> No Relationships’ ;

 WHILE @@FETCH_STATUS = 0
 BEGIN

 
 PRINT ‘=====> switching delete rule on ‘ + @ForeignKey + ‘ to CASCADE’;
 
 BEGIN TRANSACTION
 BEGIN TRY
 EXEC(‘
 
 ALTER TABLE [‘+@TableOwner+’].[‘ + @TableName + ‘]
     DROP CONSTRAINT ‘+@ForeignKey+’;
 
 ALTER TABLE [‘+@TableOwner+’].[‘ + @TableName + ‘] ADD CONSTRAINT
 ‘+@ForeignKey+’ FOREIGN KEY
 (
 ‘+@ColumnName+’
 ) REFERENCES ‘+@ReferenceTableName+’
 (
 ‘+@ReferenceColumnName+’
 ) ON DELETE CASCADE;
 ‘);
 COMMIT TRANSACTION
 END TRY
 BEGIN CATCH
 PRINT ‘=====> can”t switch ‘ + @ForeignKey + ‘ to CASCADE, – ‘ +
 CAST(ERROR_NUMBER() AS VARCHAR) + ‘ – ‘ + ERROR_MESSAGE();
 ROLLBACK TRANSACTION
 END CATCH;
 
 
 FETCH NEXT FROM DataBaseTableRelationships INTO @ForeignKey, @ColumnName, @ReferenceTableName, @ReferenceColumnName;
 END;

 CLOSE DataBaseTableRelationships;
 DEALLOCATE DataBaseTableRelationships;
 
 END
 PRINT ”;
 PRINT ”;
 
 FETCH NEXT FROM DataBaseTables0
 INTO @TableOwner,@TableName;
END
CLOSE DataBaseTables0;
DEALLOCATE DataBaseTables0;

PRINT(‘Loop though each table and DELETE All data from the table’)
DECLARE DataBaseTables1 CURSOR FOR
SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name
FROM sys.tables AS t;

OPEN DataBaseTables1;

FETCH NEXT FROM DataBaseTables1
INTO @TableOwner,@TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
 
 IF (NOT EXISTS(SELECT TOP 1 1 FROM @IgnoreTables WHERE TableName = @TableName))
 BEGIN
 PRINT ‘[‘+@TableOwner+’].[‘ + @TableName + ‘]’;
 
 
 PRINT ‘=====> deleting data from [‘+@TableOwner+’].[‘ + @TableName + ‘]’;
 
 BEGIN TRY
 EXEC(‘
 
 DELETE FROM [‘+@TableOwner+’].[‘ + @TableName + ‘]
 ‘);
 END TRY
 BEGIN CATCH
 PRINT ‘=====> can”t FROM [‘+@TableOwner+’].[‘ + @TableName + ‘], – ‘ +
 CAST(ERROR_NUMBER() AS VARCHAR) + ‘ – ‘ + ERROR_MESSAGE();
 END CATCH;
 END
 PRINT ”;
 PRINT ”;
 
 FETCH NEXT FROM DataBaseTables1
 INTO @TableOwner,@TableName;
END
CLOSE DataBaseTables1;
DEALLOCATE DataBaseTables1;
 
 
 
 
 
 
PRINT(‘Loop through all tables and switch all constraints to have a delete rule they had at the beggining of the task’)
DECLARE DataBaseTables2 CURSOR FOR
SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name
FROM sys.tables AS t;

OPEN DataBaseTables2;

FETCH NEXT FROM DataBaseTables2
INTO @TableOwner,@TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
 
 IF (NOT EXISTS(SELECT TOP 1 1 FROM @IgnoreTables WHERE TableName = @TableName))
 BEGIN
 PRINT ‘[‘+@TableOwner+’].[‘ + @TableName + ‘]’;
 
 DECLARE DataBaseTableRelationships CURSOR FOR
 SELECT ForeignKey, ColumnName, ReferenceTableName, ReferenceColumnName, DeleteRule
 FROM @AllRelationships
 WHERE TableName = @TableName

 OPEN DataBaseTableRelationships;
 FETCH NEXT FROM DataBaseTableRelationships INTO @ForeignKey, @ColumnName, @ReferenceTableName, @ReferenceColumnName, @DeleteRule;

 IF @@FETCH_STATUS <> 0
 PRINT ‘=====> No Relationships’ ;

 WHILE @@FETCH_STATUS = 0
 BEGIN
 DECLARE @switchBackTo varchar(50) =
 CASE
 WHEN @DeleteRule = ‘NO_ACTION’ THEN ‘NO ACTION’
 WHEN @DeleteRule = ‘CASCADE’ THEN ‘CASCADE’
 WHEN @DeleteRule = ‘SET_NULL’ THEN ‘SET NULL’
 WHEN @DeleteRule = ‘SET_DEFAULT’ THEN ‘SET DEFAULT’
 END
 
 PRINT ‘=====> switching delete rule on ‘ + @ForeignKey + ‘ to ‘ + @switchBackTo;

 BEGIN TRANSACTION
 BEGIN TRY
 EXEC(‘
 
 ALTER TABLE [‘+@TableOwner+’].[‘ + @TableName + ‘]
     DROP CONSTRAINT ‘+@ForeignKey+’;
 
 ALTER TABLE [‘+@TableOwner+’].[‘ + @TableName + ‘] ADD CONSTRAINT
 ‘+@ForeignKey+’ FOREIGN KEY
 (
 ‘+@ColumnName+’
 ) REFERENCES ‘+@ReferenceTableName+’
 (
 ‘+@ReferenceColumnName+’
 ) ON DELETE ‘+@switchBackTo+’
 
 ‘);
 COMMIT TRANSACTION
 END TRY
 BEGIN CATCH
 PRINT ‘=====> can”t change ‘+@ForeignKey + ‘ back to ‘+ @switchBackTo +’, – ‘ +
 CAST(ERROR_NUMBER() AS VARCHAR) + ‘ – ‘ + ERROR_MESSAGE();
 ROLLBACK TRANSACTION
 END CATCH;
 
 FETCH NEXT FROM DataBaseTableRelationships INTO @ForeignKey, @ColumnName, @ReferenceTableName, @ReferenceColumnName, @DeleteRule;
 END;

 CLOSE DataBaseTableRelationships;
 DEALLOCATE DataBaseTableRelationships;
 
 END
 PRINT ”;
 PRINT ”;
 
 FETCH NEXT FROM DataBaseTables2
 INTO @TableOwner,@TableName;
END
CLOSE DataBaseTables2;
DEALLOCATE DataBaseTables2;

Mustafa EL-Masry Script

Check if column is Exists in table or not exists


declare @count int

Declare @table nvarchar (50)

declare @Column nvarchar (100)

set @table =‘checkcolumn’

set @Column =‘Salary’

set @count =(Select count(*) fromInformation_Schema.columns

where Table_name = @table and Column_name =@Column)

if (@count=1)

print ‘The Column Name ‘+‘ ( ‘+ @Column +  ‘ ) ‘+‘ Is already Exists in Table Name ‘+   ‘ ( ‘+ @table +  ‘ ) ‘

if (@count = 0)

print ‘The Column Name ‘+’ ( ‘+ @Column +  ‘ ) ‘+’ Is Not Exists in Table Name ‘+   ‘ ( ‘+ @table +  ‘ )

Mustafa EL-Masry Encryption Tips

Stored Procedure Encryption in SQL Server


Because of some security policies we need to require our code to be safe from Users who are going to use SQL Server database and objects of them and some outside threat. We have different different users to access the database objects or used for application.

We have so many ways to encrypt data, but here i am talkig about the code encryption. With this encryption security, Users can execute the stored procedures but can not view the code.

Let us check the how the Stored Procedures can be encrypted.

–Create Database Demo

Create Database Demo

go

— Creating table

IF (OBJECT_ID(‘UserMaster’,‘U’)> 0)

DROP TABLE UserMaster

GO

CREATE TABLE UserMaster

( UserId INT,

UserName VARCHAR(100),

UserPwd NVARCHAR(100) )

GO

— Inserting demo records

INSERT INTO UserMaster

SELECT ‘1’,‘User1’,‘pwd1’

UNION ALL

SELECT ‘2’,‘User2’,‘pwd2’

UNION ALL

SELECT ‘3’,‘User3’,‘pwd3’

GO

———————————————-

— Creating Stored Procedure without encryption

CREATE PROCEDURE GetUserDataWithoutEncrypt

AS

BEGIN

SET NOCOUNTON

SELECT UserID , UserName , UserPwd FROM UserMaster

END

GO

— Creating Stored Procedure with encryption

CREATE PROCEDURE GetUserDataWithEncrypt

WITH ENCRYPTION

AS

BEGIN

SET NOCOUNTON

SELECT UserID , UserName , UserPwd FROM UserMaster

END

GO

————————————–

EXEC GetUserDataWithoutEncrypt

EXEC GetUserDataWithEncrypt

GO

——————————————-

EXEC SP_HELPTEXTGetUserDataWithoutEncrypt

EXEC SP_HELPTEXTGetUserDataWithEncrypt