Archive

Archive for May, 2009

DROP Foreign keys script in a Database

May 24, 2009 Leave a comment

Drop Foreign key script will generate drop script for all foreign keys within a database.
 

SET NOCOUNT ON
DECLARE @FK_Name VARCHAR(100)
DECLARE @FK_TableName VARCHAR(100)
DECLARE @SC_Name VARCHAR(10)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @vCount Int
DECLARE @vNumDBs Int
DECLARE @DB_NAME VARCHAR(500)
DECLARE @vFKeyList Table
(
SLID INT NOT NULL IDENTITY(1, 1),
FK_Name VARCHAR(100),
FK_TableName VARCHAR(100),
SC_Name VARCHAR(10)
)
INSERT INTO @vFKeyList
SELECT f.name AS FK_Name,OBJECT_NAME(f.parent_object_id) AS FK_TableName,
schema_name(f.schema_id) as SC_ID
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

Set @vNumDBs = @@RowCount
Set @vCount = 1
While @vCount <= @vNumDBs
Begin
Select @FK_Name = FK_Name,@FK_TableName=FK_TableName,
@SC_Name=SC_Name From @vFKeyList Where SLID = @vCount
SET @DB_NAME=DB_NAME()
SET @SQL=''
SET @SQL='USE [' + @DB_NAME + '] ALTER TABLE ['+ @SC_Name +'].['+ @FK_TableName + '] ' +
'DROP CONSTRAINT ['+@FK_Name+']'
Print @SQL
Print 'GO'
Set @vCount = @vCount + 1
End
SET NOCOUNT OFF

Sample Result
GO
USE [easy] ALTER TABLE [dbo].[ext_eri_dig_adn] DROP CONSTRAINT [FK_exteda_exte]
GO

Generate Script for all Foreign Keys in a Database

May 24, 2009 Leave a comment

The below script will generate create script for all foreign keys in a database. Variable @NoCheckVal will serve the purpose if u wants to create the script with NO CHECK option.
Create the stored procedure in the target database.

CREATE PROCEDURE Usp_dba_GenFKScript (@NoCheckVal char(2))
AS
BEGIN
SET NOCOUNT ON
DECLARE @FK_Name VARCHAR(100)
DECLARE @FK_TableName VARCHAR(100)
DECLARE @FK_ColumnName VARCHAR(50)
DECLARE @RF_TableName VARCHAR(100)
DECLARE @RF_ColumnName VARCHAR(50)
DECLARE @SC_Name VARCHAR(10)
DECLARE @NoCheck Char(2)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @vCount Int
DECLARE @vNumDBs Int
DECLARE @DB_NAME VARCHAR(500)
DECLARE @vFKeyList Table
(
SLID INT NOT NULL IDENTITY(1, 1),
FK_Name VARCHAR(100),
FK_TableName VARCHAR(100),
FK_ColumnName VARCHAR(50),
RF_TableName VARCHAR(100),
RF_ColumnName VARCHAR(50),
SC_Name VARCHAR(10)
)

SET @DB_NAME=DB_NAME()
INSERT INTO @vFKeyList
SELECT f.name AS FK_Name,OBJECT_NAME(f.parent_object_id) AS FK_TableName,COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS FK_ColumnName,OBJECT_NAME (f.referenced_object_id) AS RF_TableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS RF_ColumnName,
schema_name(f.schema_id) as SC_ID
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

Set @vNumDBs = @@RowCount
Set @vCount = 1
If @NoCheckVal is null
BEGIN
SET @NoCheck=”
END
ELSE
SET @NoCheck=@NoCheckVal

While @vCount <= @vNumDBs

Begin
Select @FK_Name=FK_Name,@FK_TableName=FK_TableName,
@FK_ColumnName=FK_ColumnName, @RF_TableName=RF_TableName,
@RF_ColumnName=RF_ColumnName,@SC_Name=SC_Name
FROM @vFKeyList where SLID= @vCount

SET @SQL=’USE [‘ + @DB_NAME + ‘] ALTER TABLE [‘+ @SC_Name +’].[‘+ @FK_TableName +’] WITH ‘ + @NoCheck +’CHECK ADD CONSTRAINT [‘+@FK_Name+’] FOREIGN KEY([‘+@FK_ColumnName+
‘]) REFERENCES [‘+@SC_Name+’].[‘+@RF_TableName+’] ([‘+@RF_ColumnName+’])’

Print @SQL
Print ‘GO’

SET @SQL=’USE [‘ + @DB_NAME + ‘] ALTER TABLE [‘+ @SC_Name +’].[‘+ @FK_TableName +’] CHECK CONSTRAINT [‘+@FK_Name+’]’

Print @SQL
Print ‘GO’ 

Set @vCount = @vCount + 1
End
SET NOCOUNT OFF
END

 

Script Sample Without NO Check option

EXECUTE Usp_dba_GenFKScript ”

USE [easy] ALTER TABLE [dbo].[ext_acdposition] WITH   CHECK ADD CONSTRAINT [FK_extacdp_swext] FOREIGN KEY([switch_extension]) REFERENCES [dbo].[switch_extension] ([switch_device])

GO

 

USE [easy] ALTER TABLE [dbo].[ext_acdposition] CHECK CONSTRAINT [FK_extacdp_swext]

GO

Script Sample With No Check option

EXECUTE Usp_dba_GenFKScript ‘NO’

USE [easy] ALTER TABLE [dbo].[ext_acdposition] WITH  NOCHECK ADD CONSTRAINT [FK_extacdp_swext] FOREIGN KEY([switch_extension]) REFERENCES [dbo].[switch_extension] ([switch_device])
GO
USE [easy] ALTER TABLE [dbo].[ext_acdposition] CHECK CONSTRAINT [FK_extacdp_swext]
GO

MERGE RANGE PARTITION SQL SERVER 2005

May 17, 2009 Leave a comment

Merging Partition states moving the data into an exiting partition or to new file group (Forward only means to next file group or Partition).

 

The merged partition resides in the filegroup that originally did not hold boundary_value. boundary_value is a constant expression that can reference variables (including user-defined type variables) or functions (including user-defined functions). It cannot reference a Transact-SQL expression. boundary_value must either match or be implicitly convertible to the data type of its corresponding partitioning column, and cannot be truncated during implicit conversion in a way that the size and scale of the value does not match that of its corresponding input_parameter_type. (BOL)

 

 

Alter partition function easydb_partition_yearly_function()

merge range(‘2008-01-31 23:59:59.997’)

SPLIT RANGE PARTITION SQL SERVER 2005

May 17, 2009 1 comment

SPLIT RANGE PARTITION.

SPLIT RANGE Partition splits the data from exiting partition to new added Filegroup or partition.

Let’s say we have an existing database partition setup, we need to move some of the data from one partition to newly added partition.

Steps 

1. Create a New FileGroup

USE [master]

GO

ALTER DATABASE [xxx] ADD FILEGROUP [FG2008_Arch]

2. Add File to Newly Created FileGroup

ALTER DATABASE [easy] ADD FILE

( NAME = N’PSCH2008_Arch’,

      FILENAME = N’I:\MSSQL.1\MSSQL\DATA\PSCH2008_Arch.ndf’ ,

      SIZE = 2048KB , FILEGROWTH = 1024KB )

TO FILEGROUP [FG2008_Arch]

GO

Let say we want to partition Year 2008 Data to my newly created FileGroup.

To do so we need to Adds a filegroup to a partition scheme or alters the designation of the NEXT USED filegroup for the partition scheme.

ALTER PARTITION SCHEME easydb_partition_yearly_schema

NEXT USED ‘FG2008_Arch’

Every partition have a boundry set so we need to set the split boundry

ALTER PARTITION FUNCTION easydb_partition_yearly_function()

SPLIT RANGE (‘2008-06-31 23:59:59.997’)

All data on and before of date and time ‘2008-06-31 23:59:59.997’  will move to new filegroup.

To check partiton has splited check by executing the below code

 

SELECT * FROM sys.data_spaces 

–Get the data_space_id of the newly created filegroup

 

SELECT count(*) FROM dbo.tablename where $Partition.easydb_partition_yearly_function(partitioned columnname)=data_space_id of the newly created filegroup

 

Now Set back the Next Used Partition

ALTER PARTITION SCHEME easydb_partition_yearly_schema

NEXT USED ‘FG2009’