RSS

Monthly Archives: August 2011

How to get information about all databases

‎/*
**********For All Database Administrator (DBA)******************
*****How to get information about all databases without a loop********
1-ndexes in all databases with their usage (1 Script)
2-Indexes in all databases with their physical stats (1 Script)
3-Count of all objects in all databases (1 Script)
4-Quick Record Count in All Tables in All Databases (2 Script)
5-All Schema Names in All Databases (1 Script)
6-List of All Tables in All Databases (2 Script)
7-LIST OF ALL procedures IN ALL DATABASES (1 Script)
8-LIST OF ALL VIEWS IN ALL DATABASES (1 Script)
9- Size Tales in Database Return
 (Count Of Rows,Reserved Size,Data Size,Index Size,UnUsed Size) (2 Script)
10-Database File Size (MDF.LDF.NDF)in ALL Databases (1 Script)
11-Database SIZE (2 Script)
*/
——————————————————————————————
—How do I know which version of SQL Server I’m running?
SELECT ‘SQL Server ‘
+ CAST(SERVERPROPERTY(‘productversion’) AS VARCHAR) + ‘ – ‘
+ CAST(SERVERPROPERTY(‘productlevel’) AS VARCHAR) + ‘ (‘
+ CAST(SERVERPROPERTY(‘edition’) AS VARCHAR) + ‘)’
—————————————————————————————————–
–Some Info to all databases
SELECT database_id,
CONVERT(VARCHAR(25), DB.name) AS dbName,
CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, ‘status’)) AS [Status],
state_desc,
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘rows’) AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘rows’) AS [Data MB],
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘log’) AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = ‘log’) AS [Log MB],
user_access_desc AS [User access],
recovery_model_desc AS [Recovery model],
CASE compatibility_level
WHEN 60 THEN ’60 (SQL Server 6.0)’
WHEN 65 THEN ’65 (SQL Server 6.5)’
WHEN 70 THEN ’70 (SQL Server 7.0)’
WHEN 80 THEN ’80 (SQL Server 2000)’
WHEN 90 THEN ’90 (SQL Server 2005)’
WHEN 100 THEN ‘100 (SQL Server 2008)’
END AS [compatibility level],
CONVERT(VARCHAR(20), create_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],
— last backup
ISNULL((SELECT TOP 1
CASE TYPE WHEN ‘D’ THEN ‘Full’ WHEN ‘I’ THEN ‘Differential’ WHEN ‘L’ THEN ‘Transaction log’ END + ‘ – ‘ +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ‘ days ago’, ‘NEVER’)) + ‘ – ‘ +
CONVERT(VARCHAR(20), backup_start_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), backup_start_date, 108) + ‘ – ‘ +
CONVERT(VARCHAR(20), backup_finish_date, 103) + ‘ ‘ + CONVERT(VARCHAR(20), backup_finish_date, 108) +
‘ (‘ + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ‘ ‘
+ ‘seconds)’
FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),’-‘) AS [Last backup],
CASE WHEN is_fulltext_enabled = 1 THEN ‘Fulltext enabled’ ELSE ” END AS [fulltext],
CASE WHEN is_auto_close_on = 1 THEN ‘autoclose’ ELSE ” END AS [autoclose],
page_verify_option_desc AS [page verify option],
CASE WHEN is_read_only = 1 THEN ‘read only’ ELSE ” END AS [read only],
CASE WHEN is_auto_shrink_on = 1 THEN ‘autoshrink’ ELSE ” END AS [autoshrink],
CASE WHEN is_auto_create_stats_on = 1 THEN ‘auto create statistics’ ELSE ” END AS [auto create statistics],
CASE WHEN is_auto_update_stats_on = 1 THEN ‘auto update statistics’ ELSE ” END AS [auto update statistics],
CASE WHEN is_in_standby = 1 THEN ‘standby’ ELSE ” END AS [standby],
CASE WHEN is_cleanly_shutdown = 1 THEN ‘cleanly shutdown’ ELSE ” END AS [cleanly shutdown]
FROM sys.databases DB
ORDER BY dbName, [Last backup] DESC, NAME
———————————————————————————————
—–1. To list down all databases in the server—–
SELECT NAME AS [DataBase Name] FROM SYS.DATABASES ORDER BY NAME
———————————————————————————————-
–2. To list down all databases in the server along with the state(online / offline / restoring / — recovering / recovering_pending / suspect / emergency)—–
SELECT NAME AS [Database Name],STATE_DESC AS [Database Status]  FROM SYS.DATABASES  ORDER BY NAME
———————————————————————————
—–3. To check the state of a particular database—–
SELECT NAME AS [Database Name],STATE_DESC AS [Database State]
FROM SYS.DATABASES WHERE NAME = ‘RGEN2011’
———————————————————————————————————-

–Indexes in all databases with their usage
DECLARE @SQL NVARCHAR(MAX)
IF OBJECT_ID(‘tempdb..#Result’,’U’) IS not NULL
DROP TABLE #Result
CREATE TABLE #Result (DBName sysname, TableName Sysname, IndexName sysname, USAGE BIGINT)

SELECT @SQL = COALESCE(@SQL,”) + CHAR(13) + CHAR(10) + ‘ use ‘ + QUOTENAME([Name]) + ‘;
insert into #Result select ‘ + QUOTENAME([Name],””) + ‘ as DbName,
object_name(i.object_id) as tablename, i.name as indexname,
s.user_seeks + s.user_scans + s.user_lookups + s.user_updates as usage
from sys.indexes i
inner join sys.dm_db_index_usage_stats s
on s.object_id = i.object_id
and s.index_id = i.index_id
and s.database_id = db_id()
where objectproperty(i.object_id, ”IsUserTable”) = 1
and i.index_id > 0
order by usage;’ FROM sys.databases
–print (@SQL)
EXECUTE (@SQL)
SELECT * FROM #Result ORDER BY [DbName],[USAGE]
DROP TABLE #Result
———————————————————————
–Indexes in all databases with their physical stats
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = ”
SELECT @SQL = @SQL +
‘Select ‘ + QUOTENAME(name,””) + ‘ as [DB Name],
object_Name(PS.Object_ID,’ + CONVERT(VARCHAR(10),database_id) + ‘) as [Object],
I.Name as [Index Name], PS.Partition_Number, PS.Index_Type_Desc,
PS.alloc_unit_type_desc, PS.index_depth, PS.index_level,
PS.avg_fragmentation_in_percent, PS.fragment_count, PS.avg_fragment_size_in_pages,
PS.page_count, PS.avg_page_space_used_in_percent, PS.record_count,
PS.ghost_record_count, PS.version_ghost_record_count,
PS.min_record_size_in_bytes, PS.max_record_size_in_bytes, PS.avg_record_size_in_bytes,
PS.forwarded_record_count, PS.compressed_page_count
from ‘ + QUOTENAME(name) + ‘.sys.dm_db_index_physical_stats(‘ +
CONVERT(VARCHAR(10),database_id) + ‘, NULL, NULL, NULL, NULL) PS
INNER JOIN ‘ + QUOTENAME(name) +
‘.sys.Indexes I on PS.Object_ID = I.Object_ID and PS.Index_ID = I.Index_ID ‘
+ CHAR(13)

FROM sys.databases WHERE state_desc = ‘ONLINE’

EXECUTE(@SQL)
———————————————————————————-
–Count of all objects in all databases
DECLARE @Qry NVARCHAR(MAX)
SELECT @Qry = COALESCE(@Qry + CHAR(13) + CHAR(10) + ‘ UNION ALL ‘,”) + ‘
select ‘ + QUOTENAME([Name],””) + ‘ as DBName, [AGGREGATE_FUNCTION], [CHECK_CONSTRAINT],[DEFAULT_CONSTRAINT],

[FOREIGN_KEY_CONSTRAINT],

[SQL_SCALAR_FUNCTION],

[CLR_SCALAR_FUNCTION],

[CLR_TABLE_VALUED_FUNCTION],

[SQL_INLINE_TABLE_VALUED_FUNCTION],

[INTERNAL_TABLE],[SQL_STORED_PROCEDURE],[CLR_STORED_PROCEDURE],[PLAN_GUIDE],[PRIMARY_KEY_CONSTRAINT],
[RULE],[REPLICATION_FILTER_PROCEDURE],[SYNONYM],[SERVICE_QUEUE],[CLR_TRIGGER],[SQL_TABLE_VALUED_FUNCTION],[SQL_TRIGGER],
[TABLE_TYPE],[USER_TABLE],[UNIQUE_CONSTRAINT],[VIEW],[EXTENDED_STORED_PROCEDURE]

from (select [Name], type_Desc from ‘ + QUOTENAME([Name]) + ‘.sys.objects where is_ms_shipped = 0) src
PIVOT (count([Name]) FOR type_desc in ([AGGREGATE_FUNCTION], [CHECK_CONSTRAINT],[DEFAULT_CONSTRAINT],

[FOREIGN_KEY_CONSTRAINT],

[SQL_SCALAR_FUNCTION],

[CLR_SCALAR_FUNCTION],

[CLR_TABLE_VALUED_FUNCTION],

[SQL_INLINE_TABLE_VALUED_FUNCTION],

[INTERNAL_TABLE],[SQL_STORED_PROCEDURE],[CLR_STORED_PROCEDURE],[PLAN_GUIDE],[PRIMARY_KEY_CONSTRAINT],
[RULE],[REPLICATION_FILTER_PROCEDURE],[SYNONYM],[SERVICE_QUEUE],[CLR_TRIGGER],[SQL_TABLE_VALUED_FUNCTION],[SQL_TRIGGER],
[TABLE_TYPE],[USER_TABLE],[UNIQUE_CONSTRAINT],[VIEW],[EXTENDED_STORED_PROCEDURE])) pvt’ FROM sys.databases
WHERE [name] NOT IN (‘master’,’tempdb’,’model’,’msdb’) ORDER BY [Name]

EXECUTE(@Qry)
————————————————————————-
–Quick Record Count in All Tables in All Databases
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = ”
–select * from sys.databases
SELECT @SQL = @SQL + CHAR(13) + ‘USE ‘ + QUOTENAME([name]) + ‘;
SELECT ‘ +QUOTENAME([name],””) + ‘as [Database Name],
SchemaName=s.name
,TableName=t.name
,CreateDate=t.create_date
,ModifyDate=t.modify_date
,p.rows
,DataInKB=sum(a.used_pages)*8
FROM sys.schemas s
JOIN sys.tables t on s.schema_id=t.schema_id
JOIN sys.partitions p on t.object_id=p.object_id
JOIN sys.allocation_units a on a.container_id=p.partition_id
GROUP BY s.name, t.name, t.create_date, t.modify_date, p.rows
ORDER BY SchemaName, TableName’ FROM sys.databases

EXECUTE (@SQL)
————————————
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = ”
–select * from sys.databases
SELECT @SQL = @SQL + CHAR(13) + ‘USE ‘ + QUOTENAME([name]) + ‘;
SELECT ‘ +QUOTENAME([name],””) + ‘as [Database Name], so.name AS [Table Name],
rows AS [RowCount]
FROM sysindexes AS si
join sysobjects AS so on si.id = so.id
WHERE indid IN (0,1)
AND xtype = ”U”’ FROM sys.databases

EXECUTE (@SQL)
————————————————————————————–
—All Schema Names in All Databases
use master
go
DECLARE @SQL NVARCHAR(MAX)
CREATE TABLE AllDBSchemas ([DB Name] sysname, [SCHEMA Name] sysname)

SELECT @SQL = COALESCE(@SQL,”) + ‘
insert into AllDBSchemas

select ‘ + QUOTENAME(name,””) + ‘ as [DB Name], [Name] as [Schema Name] from ‘ +
QUOTENAME(Name) + ‘.sys.schemas order by [DB Name],[Name];’ FROM sys.databases
ORDER BY name

EXECUTE(@SQL)

SELECT * FROM AllDBSchemas
–where [DB Name] = ‘FileStreamDB’ –Option To Select One Database Or More As you Like
ORDER BY [DB Name],[SCHEMA NAME]
drop table AllDBSchemas

——————————————————————————————-
–List of All Tables in All Databases
use MASTER
GO
CREATE TABLE AllTables ([DB Name] sysname, [SCHEMA Name] sysname, [TABLE Name] sysname)

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = COALESCE(@SQL,”) + ‘
insert into AllTables

select ‘ + QUOTENAME(name,””) + ‘ as [DB Name], [Table_Schema] as [Table Schema], [Table_Name] as [Table Name] from ‘ +
QUOTENAME(Name) + ‘.INFORMATION_SCHEMA.Tables;’ FROM sys.databases
ORDER BY name

EXECUTE(@SQL)

SELECT * FROM AllTables
–WHERE [DB NAME] = ‘YOUR_DATABASE NAME’ — OPTION
ORDER BY [DB Name],[SCHEMA NAME], [TABLE Name]
drop table AllTables
—————————
-2
USE MASTER
GO
IF OBJECT_ID(‘TempDB..#AllTables’,’U’) IS NOT NULL DROP TABLE #AllTables
CREATE TABLE #AllTables ([DB Name] sysname, [SCHEMA Name] NVARCHAR(128) NULL, [TABLE Name] sysname, create_date DATETIME, modify_date DATETIME)

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = COALESCE(@SQL,”) + ‘USE ‘ + QUOTENAME(name) + ‘
insert into #AllTables
select ‘ + QUOTENAME(name,””) + ‘ as [DB Name], schema_name(schema_id) as [Table Schema], [Name] as [Table Name], Create_Date, Modify_Date
from ‘ +
QUOTENAME(Name) + ‘.sys.Tables;’ FROM sys.databases
ORDER BY name
–print @SQL
EXECUTE(@SQL)
SELECT * FROM #AllTables
–WHERE [DB NAME] = ‘YOUR DATABASE NAME’ — OPTION
ORDER BY [DB Name],[SCHEMA NAME], [TABLE Name]
—————————————————————————————–
–LIST OF ALL procedures IN ALL DATABASES
IF OBJECT_ID(‘TempDB..#SPList’,’U’) IS NOT NULL DROP TABLE #SPList
CREATE TABLE #SPList ([DB Name] sysname, [SP Name] sysname,
create_date DATETIME, modify_date DATETIME)

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = ”
SELECT @SQL = @SQL + ‘ insert into #SPList
select ‘ + QUOTENAME(name, ””) + ‘, name, create_date, modify_date
from ‘ + QUOTENAME(name) + ‘.sys.procedures’ FROM sys.databases

EXECUTE (@SQL)

SELECT * FROM #SPList
–WHERE [DB NAME] = ‘YOUR DATABASE NAME’ — OPTION
ORDER BY [DB Name], [SP Name]
————————————————————–
–LIST OF ALL VIEWS IN ALL DATABASES
IF OBJECT_ID(‘TempDB..#SPList’,’U’) IS NOT NULL DROP TABLE #SPList
CREATE TABLE #SPList ([DB Name] sysname, [SP Name] sysname,
create_date DATETIME, modify_date DATETIME)

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = ”
SELECT @SQL = @SQL + ‘ insert into #SPList
select ‘ + QUOTENAME(name, ””) + ‘, name, create_date, modify_date
from ‘ + QUOTENAME(name) + ‘.sys.VIEWS’ FROM sys.databases

EXECUTE (@SQL)

SELECT * FROM #SPList
–WHERE [DB NAME] = ‘YOUR DATABASE NAME’ — OPTION
ORDER BY [DB Name], [SP Name]
——————————————————————————
–Size Tales in Database Return(Count Of Rows,Reserved Size,Data Size,Index Size,UnUsed Size)
exec sp_MSforeachtable ‘print ”?” exec sp_spaceused ”?”’
IF OBJECT_ID(‘tempdb..#TablesSizes’) IS NOT NULL
DROP TABLE #TablesSizes

CREATE TABLE #TablesSizes (TableName sysname, ROWS BIGINT, reserved VARCHAR(100), DATA VARCHAR(100), index_size VARCHAR(100), unused VARCHAR(100))

DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL,”) + ‘
insert into #TablesSizes execute sp_spaceused ‘ + QUOTENAME(Table_Name,””) FROM INFORMATION_SCHEMA.TABLES

print (@SQL)
EXECUTE (@SQL)

SELECT * FROM #TablesSizes ORDER BY TableName
————————————————————————————————
–Database File Size (MDF.LDF.NDF)in ALL Databases
CREATE TABLE #FileSizes (DBName sysname, [FILE Name] VARCHAR(MAX), [Physical Name] VARCHAR(MAX),
SIZE DECIMAL(12,2))
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = ”
SELECT @SQL = @SQL + ‘USE’ + QUOTENAME(name) + ‘
insert into #FileSizes
select ‘ + QUOTENAME(name,””) + ‘, Name, Physical_Name, size/1024.0 from sys.database_files ‘
FROM sys.databases

EXECUTE (@SQL)
SELECT * FROM #FileSizes ORDER BY DBName, [FILE Name]
Drop Table #FileSizes
———————————————–
-2
use master
go
CREATE TABLE #FileSizes (DBName sysname, [FILE Name] VARCHAR(MAX), [Physical Name] VARCHAR(MAX),
SIZE DECIMAL(12,2))
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = ”
SELECT @SQL = @SQL + ‘USE’ + QUOTENAME(name) + ‘
insert into #FileSizes
select ‘ + QUOTENAME(name,””) + ‘, Name, Physical_Name, size/1024.0 from sys.database_files ‘
FROM sys.databases

EXECUTE (@SQL)
SELECT * FROM #FileSizes ORDER BY DBName, [FILE Name]
drop table #FileSizes
——————————————————————————————–
—Database SIZE
DECLARE @SQL1 VARCHAR(MAX)
SELECT @SQL1 =COALESCE(@SQL1 + CHAR(13) + ‘UNION ALL
‘ ,”) + ‘SELECT ”’ + name + ”’ AS DBNAME,’ +
‘sum(size * 8 /1024.0) AS MB from ‘ + QUOTENAME(name) + ‘.dbo.sysfiles’
FROM sys.databases
ORDER BY name

EXECUTE (@SQL1)
——————————-
–2
/*
The first select statement is to get how many kilobytes a data page has.
SQL Server allocates disk space in the unit of data page. Currently each
SQL server data page contains 8k bytes. The number of data pages allocated
to each database file is recorded in the sysfiles system table.
With this information on hand the script creates a temporary table #tem and
update the temporary table with size information which is gathered by querying the sysfiles table.
*/
use master

declare @PageSize varchar(10)
select @PageSize= v.low/1024.0
from master..spt_values v
where v.number=1 and v.type=’E’
–select * from master..spt_values v
select name as DatabaseName, convert(float,null) as Size
into #tem
From sysdatabases

declare @SQL varchar (8000)
set @SQL=”

while exists (select * from #tem where size is null)
begin
select @SQL=’update #tem set size=(select round(sum(size)*’+@PageSize+’/1024,0) From ‘+quotename(databasename)+’.dbo.sysfiles) where databasename=”’+databasename+””
from #tem
where size is null
exec (@SQL)
end

select * from #tem order by DatabaseName
drop table #tem

————————————————————————-
—Backup All Databases with Compression (SQL 2008)
DECLARE @ToExecute VARCHAR(8000)

SELECT @ToExecute = COALESCE(@ToExecute + ‘Backup Database ‘ + QUOTENAME([Name]) +
‘ To Disk = ”E:\Backups\’ + [Name] + ‘.bak”
WITH NOFORMAT, NOINIT,
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10’ + CHAR(13),”)

FROM sys.databases

WHERE [Name] Not In (‘tempdb’) and DATABASEPROPERTYEX ([Name],’Status’) = ‘online’

EXECUTE (@ToExecute)
———————————————————————————————

 
 

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/

 

 
1 Comment

Posted by on August 30, 2011 in 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;

 
 

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 +  ‘ )

 
Leave a comment

Posted by on August 30, 2011 in Script

 

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

 
Leave a comment

Posted by on August 30, 2011 in Encryption Tips