Category Archives: Scripts

How to restore a table from a Full backup using Quest Litespeed without restoring the entire database

In this article I will demonstrate how we can restore a table from a SQL Server database backup file which has been taken by Quest Litespeed tool. This feature called Object level recovery in the tool is extremely helpful as you need not restore the entire database and yet retrieve data from the backup. Follow the below mentioned steps and understand how this can be accomplished. I will be using a trial copy of Quest Litespeed along with SQL server 2012 and the database we would use is Adventureworks2012. However you can use SQL server 2008 also.

Step 1: Create a database and take a full backup of the database
Let us create a database for this purpose

CREATE DATABASE MyDB
GO
USE mydb
GO
CREATE TABLE Mytable1
( id INT,FNAME NVARCHAR(50) )
GO
INSERT INTO Mytable1 VALUES (1,'Sanchayan')
GO
INSERT INTO Mytable1 VALUES (2,'Abhishek')
go
CREATE TABLE Mytable2
( id INT,LNAME NVARCHAR(50) )
GO
INSERT INTO Mytable2 VALUES (1,'Pandit')
GO
INSERT INTO Mytable2 VALUES (2,'Agrawal')
go

Now take a Full Backup of the database.

exec master.dbo.xp_backup_database
@database = N'Mydb',
@compressionlevel = 3,
@filename = N'D:\MSSQL\SQLBackup\Mydb.bak',
@init = 0,
@OLRMAP = 1--This is important as this marks the backup for object level recovery
GO

Step 2: Now run the following code to find the Tables available in the backup

Use Master
go
exec xp_objectrecovery_viewcontents
@FileName = N'D:\MSSQL\SQLBackup\mydb.bak',
@FileNumber = 1,
@Type = 'All',
@DisableLogProcessing = 1

olr1
Step 3: Now select the table that you wish to restore. In this demo we would restore MyTable2.
We would need the below predefined:
a) The database where we would restore the table.
b) A temp directory location which should be large enough for the table to be restored initially.

Use master
go
exec xp_objectrecovery
@FileName = N'D:\MSSQL\SQLBackup\Mydb.bak',
@FileNumber = 1,
@ObjectName = N'dbo.Mytable2',
@DestinationTable = N'[dbo].[Mytable2]',
@DestinationDatabase = N'MyTempDB',
@DestinationServer = N'GN-A-MOOKBO-7L\sql2012',
@TempDirectory = N'D:\MSSQL\SQLBackup',
@DisableLogProcessing = 1

The output of the above code would be as below
olr2To verify if the data you can query the table that we restored on mytempdb database
olr3I hope this article was helpful in understanding how we can use ‘Object level recovery’ to fetch data from a full backup file that has been taken using Quest Litespeed for SQL Server tool.

Technical reference: http://www.quest.com/litespeed-for-sql-server/

How to Partition a table in SQL Server

In this article we will see how we can partition a table in a database using T-SQL. We will first understand the basics of table partitioning in SQL Server and then go through the scripts that we will use to build a partitioned table. Let us start with the basics of partitioning.

What is table partitioning in SQL Server?
Partitioning is dividing a large table and its indexes into smaller fragments called partitions.

Some of the Benefits?
Its helps performing maintenance operation fragment by fragment basis rather that performing on the entire table. Apart from that SQL queries can be redirected to proper partitions directly rather than scanning the entire table.SQL queries that are properly filtered on the partition column can perform better by making use of partition elimination and parallelism. Archiving data is another major benefit of partitioning. We will not deep dive in this article on the benefits of partitioning.

Components of Partitioning:
1) Partition Function(PF): PF defines which rows goes into what partition in a partitioned table based on a range.
2) Partition Scheme(PS):The partition scheme defines how partitions will be stored on filegroups. Creating a partition scheme assumes that your database already has filegroups.

Let us now understand with an example how we can partition a table in SQL server.
1) Creating a database:

USE master
GO
CREATE DATABASE PartitionDB
ON PRIMARY (NAME = N'PartitionDB'
,FILENAME = N'D:\MSSQL\Data\PartitionDB.mdf'
,SIZE = 50MB, FILEGROWTH = 150MB)
LOG ON (
NAME = N'PartitionDB_log'
,FILENAME = N'D:\MSSQL\Logs\PartitionDB_log.ldf'
,SIZE = 10MB, FILEGROWTH = 100MB);
GO

2) Adding 4 new filegroups to the PartitionDB database.

ALTER DATABASE PartitionDB ADD FILEGROUP PartitionFG1;
GO
ALTER DATABASE PartitionDB ADD FILEGROUP PartitionFG2;
GO
ALTER DATABASE PartitionDB ADD FILEGROUP PartitionFG3;
GO
ALTER DATABASE PartitionDB ADD FILEGROUP PartitionFG4;
GO

3) Adding files to the database filegroups.

 ALTER DATABASE PartitionDB
    ADD FILE
    (
        NAME = PartitionFile1,
        FILENAME = 'D:\MSSQL\Data\PartitionFile1.ndf',
        SIZE = 20MB, MAXSIZE = 50MB, FILEGROWTH = 5MB
    )
    TO FILEGROUP PartitionFG1;
GO
 ALTER DATABASE PartitionDB
    ADD FILE
    (
        NAME = PartitionFile2,
        FILENAME = 'D:\MSSQL\Data\PartitionFile2.ndf',
        SIZE = 20MB, MAXSIZE = 50MB, FILEGROWTH = 5MB
    )
    TO FILEGROUP PartitionFG2;
GO
 ALTER DATABASE PartitionDB
    ADD FILE
    (
        NAME = PartitionFile3,
        FILENAME = 'D:\MSSQL\Data\PartitionFile3.ndf',
        SIZE = 20MB, MAXSIZE = 50MB, FILEGROWTH = 5MB
    )
    TO FILEGROUP PartitionFG3;
GO

 

 ALTER DATABASE PartitionDB
    ADD FILE
    (
        NAME = PartitionFile4,
        FILENAME = 'D:\MSSQL\Data\PartitionFile4.ndf',
        SIZE = 20MB, MAXSIZE = 50MB, FILEGROWTH = 5MB
    )
    TO FILEGROUP PartitionFG4;
GO

4) Creating a Partition Function(PF):

 CREATE PARTITION FUNCTION PartFunc1 (int)
    AS RANGE LEFT FOR VALUES (10, 20, 30);
GO

5) Creating a Partition Scheme(PS):

CREATE PARTITION SCHEME PartScheme1
    AS PARTITION PartFunc1
    TO (PartitionFG1, PartitionFG2,PartitionFG3,PartitionFG4);
GO

6) Create a Table:–

USE [PartitionDB]
GO
CREATE TABLE PartitionTable
	(
	MyID int NOT NULL,
	MyDate datetime NULL,
	Name varchar(50) NULL
	)  ON PartScheme1(MyID)
GO

7) Create Index on Partitioned Table

USE PartitionDB
go
CREATE UNIQUE CLUSTERED INDEX IX_PartitionTable
ON PartitionTable(MyID)
ON PartScheme1 (MyID);
GO

8) Insert Data into the Table

USE PartitionDB
go
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (1,GETDATE(),'Rooney');
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (11,GETDATE(),'Van persie');
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (22,GETDATE(),'De Gea');
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (34,GETDATE(),'Moyes');
GO

9) Verify data in the table

SELECT * FROM dbo.partitiontable

10) Verify Rows Inserted in Partitions

USE PartitionDB
go
SELECT * FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='partitiontable';
GO

P1
11) Run the below code to see the details of the partitioned table

USE PartitionDB
GO
SELECT
OBJECT_NAME(idx.object_id) AS TableName ,
psh.name AS PartitionSchemeName ,
fnc.name AS PartitionFunctionName,
part.partition_number AS PartitionNumber ,
fg.name AS [Filegroup],
rows AS 'No of Records' ,
CASE boundary_value_on_right WHEN 1 THEN 'less than'
ELSE 'less than or equal to' END AS 'Condition',
value AS 'Range' ,
part.partition_id AS [Partition Id] FROM sys.partitions part
JOIN sys.indexes idx
ON part.object_id = idx.object_id
AND part.index_id = idx.index_id JOIN sys.partition_schemes psh
ON psh.data_space_id = idx.data_space_id
JOIN
sys.partition_functions fnc
ON fnc.function_id = psh.function_id LEFT
JOIN sys.partition_range_values prv
ON fnc.function_id = prv.function_id
AND part.partition_number = prv.boundary_id
JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = psh.data_space_id
AND dds.destination_id = part.partition_number
JOIN sys.filegroups fg
ON dds.data_space_id = fg.data_space_id
JOIN (SELECT container_id, sum(total_pages) as total_pages
FROM
sys.allocation_units GROUP BY container_id) AS au
ON au.container_id = part.partition_id JOIN sys.tables t ON
part.object_id = t.object_id WHERE idx.index_id < 2
ORDER BY TableName,part.partition_number;
GO

P2 I hope this article was helpful in understanding how we can create table partitions via T-SQL.

Script to find the size of all Indexes in a database

Continuing with my interest with Indexes, i wrote this script that finds the size of all indexes in a database along with the table and the filegroup on which the index resides. I have used the AdventureWorks2012 database as an example. Please replace the DB name for which you want to find the information.

USE AdventureWorks2012
go
IF OBJECT_ID('tempdb..#Indexdata', 'U') IS NOT NULL
DROP TABLE #Indexdata

DECLARE
@SizeofIndex BIGINT, @IndexID INT,
@NameOfIndex nvarchar(200),@TypeOfIndex nvarchar(50),
@ObjectID INT,@IsPrimaryKey INT,
@FGroup VARCHAR(20)

create table #Indexdata (name nvarchar(50),
IndexID int, IndexName nvarchar(200),
SizeOfIndex int, IndexType nvarchar(50),
IsPrimaryKey INT,FGroup VARCHAR(20))
DECLARE Indexloop CURSOR FOR
SELECT idx.object_id, idx.index_id, idx.name, idx.type_desc
,idx.is_primary_key,fg.name
FROM sys.indexes idx
join sys.objects so
on idx.object_id = so.object_id JOIN sys.filegroups fg
ON idx.data_space_id = fg.data_space_id
where idx.type_desc != 'Heap'
and so.type_desc not in ('INTERNAL_TABLE','SYSTEM_TABLE')

OPEN Indexloop

FETCH NEXT FROM Indexloop
INTO @ObjectID, @IndexID, @NameOfIndex,
@TypeOfIndex,@IsPrimaryKey,@FGroup

WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @SizeofIndex = sum(avg_record_size_in_bytes * record_count)
FROM sys.dm_db_index_physical_stats(DB_ID(),@ObjectID,
@IndexID, NULL, 'detailed')

insert into #Indexdata(name, IndexID, IndexName, SizeOfIndex,
IndexType,IsPrimaryKey,FGroup)
SELECT TableName = OBJECT_NAME(@ObjectID),
IndexID = @IndexID,
IndexName = @NameOfIndex,
SizeOfIndex = CONVERT(DECIMAL(16,1),(@SizeofIndex/(1024.0 * 1024))),
IndexType = @TypeOfIndex,
IsPrimaryKey = @IsPrimaryKey,
FGroup = @FGroup

FETCH NEXT FROM Indexloop
INTO @ObjectID, @IndexID, @NameOfIndex,
 @TypeOfIndex,@IsPrimaryKey,@FGroup
END
CLOSE Indexloop
DEALLOCATE Indexloop

select name as TableName, IndexName, IndexType,
SizeOfIndex AS [Size of index(MB)],
case when IsPrimaryKey = 1 then 'Yes' else 'No' End as [IsPrimaryKey]
,FGroup AS [File Group]
from #Indexdata order by SizeOfIndex DESC

Snapshot of the output below:
Indexsize

Find Top 5 executed queries ordered by execution count

I wrote this query that helps us find executed queries with most number of execution counts. The DMV that we have used in this script is sys.dm_exec_query_stats and the function that we have used is sys.dm_exec_sql_text.

SELECT TOP 5 SQLtxt.text AS 'SQL',
qstats.execution_count 
AS 'Execution Count',
qstats.total_logical_writes/DATEDIFF(second, qstats.creation_time,
GetDate()) AS 'Logical Writes Per Second',
qstats.total_physical_reads AS [Total Physical Reads],
qstats.total_worker_time/qstats.execution_count AS [Average WorkerTime],
qstats.total_worker_time AS 'Total Worker Time',
DATEDIFF(hour, qstats.creation_time,
GetDate()) AS 'TimeInCache in Hours'
FROM sys.dm_exec_query_stats AS qstats
CROSS APPLY sys.dm_exec_sql_text(qstats.sql_handle) AS SQLtxt
WHERE SQLtxt.dbid = db_id()
ORDER BY qstats.execution_count DESC

Find Top 5 expensive Queries from a Write IO perspective

Recently i wrote this query that helps us find the most expensive queries from a write IO perspective. The DMV that we have used in this script is sys.dm_exec_query_stats and the function that we have used is sys.dm_exec_sql_text.

SELECT TOP 5 sqltxt.text AS ‘SQL’, qstats.total_logical_writes AS [Total Logical Writes],
qstats.total_logical_writes/DATEDIFF(second, qstats.creation_time, GetDate()) AS ‘Logical Writes Per Second’,
qstats.execution_count AS ‘Execution Count’,
qstats.total_worker_time AS [Total Worker Time],
qstats.total_worker_time/qstats.execution_count AS [Average Worker Time],
qstats.total_physical_reads AS [Total Physical Reads],
DATEDIFF(Hour, qstats.creation_time, GetDate()) AS ‘TimeInCache in Hours’,
qstats.total_physical_reads/qstats.execution_count AS ‘Average Physical Reads’,
db_name(sqltxt.dbid) AS DatabaseName
FROM sys.dm_exec_query_stats AS qstats
CROSS APPLY sys.dm_exec_sql_text(qstats.sql_handle) AS sqltxt
WHERE sqltxt.dbid = db_id()
ORDER BY qstats.total_logical_writes DESC

Find Top 5 expensive Queries from a Read IO perspective

The below written query helps us find the most expensive queries from a read IO perspective. The DMV that we have used in this script is sys.dm_exec_query_stats and the function that we have used is sys.dm_exec_sql_text.

SELECT TOP 5 qt.text AS ‘SQL’, qstats.total_physical_reads AS ‘Total Physical Reads’, qstats.total_physical_reads/qstats.execution_count AS ‘Average Physical Reads’,    qstats.execution_count AS ‘Execution Count’,
qstats.total_worker_time/qstats.execution_count AS ‘Average Worker Time’,
qstats.total_worker_time AS ‘Total Worker Time’,
DATEDIFF(Hour, qstats.creation_time, GetDate()) AS ‘AgeInCache In Hours’,  db_name(qt.dbid) AS ‘Database Name’
FROM sys.dm_exec_query_stats AS qstats
CROSS APPLY sys.dm_exec_sql_text(qstats.sql_handle) AS qt
WHERE qt.dbid = db_id()
ORDER BY qstats.total_physical_reads DESC

SCOM -TSQL Script – Find active alerts in a SCOM managed environment

I had to write this query as SCOM dashboard doesn’t always give depict a proper view of the open active alerts in the system. This would be helpful for creating reports for SCOM or for monitoring purposes as to which alerts are open and from which management packs.

DROP TABLE #Base_State
Go
SELECT *
INTO #Base_State
from
(
SELECT aa.Id AS [AlertId],aa.TimeRaised AS [RaisedDateTime],
aa.Severity,aa.ResolutionState,
CASE WHEN aa.ResolutionState < 254 THEN
 'Open' ELSE 'closed' END AS Status
,mp.MPName AS [ManagementPack],
mp.ManagementPackId AS [MPID]
 FROM
OperationsManager.
dbo.AlertView (NOLOCK) aa
 LEFT JOIN
 OperationsManager.dbo.RuleView (NOLOCK) Rv
ON aa.MonitoringRuleId = rv.Id
JOIN OperationsManager.dbo.ManagementPack mp
ON rv.ManagementPackId = mp.ManagementPackId
WHERE aa.Severity = 2 AND
 aa.ResolutionState < 254
union
SELECT aa.Id AS [AlertId],aa.TimeRaised AS [RaisedDateTime],aa.Severity,aa.ResolutionState,
CASE WHEN aa.ResolutionState < 254
 THEN 'Open' ELSE 'closed' END AS STATUS,
mp.MPName AS [ManagementPack],
mp.ManagementPackId AS [MPID]
FROM
OperationsManager.dbo.AlertView (NOLOCK) aa JOIN OperationsManager.dbo.MonitorView (NOLOCK) Mv
ON aa.MonitoringRuleId = Mv.Id
 JOIN OperationsManager.dbo.ManagementPack mp
ON mv.ManagementPackId = mp.ManagementPackId
WHERE aa.Severity = 2 AND
 aa.ResolutionState < 254
UNION
SELECT aa.AlertGuid AS [AlertId],aa.RaisedDateTime,
aa.Severity,ars.ResolutionState,
CASE WHEN ars.ResolutionState < 254 THEN
 'Open' ELSE 'closed' END AS STATUS,
mp.ManagementPackSystemName AS [ManagementPack],
mp.ManagementPackVersionIndependentGuid AS [MPID]
FROM OperationsManagerDW.Alert.vAlert (NOLOCK) AA JOIN OperationsManagerDW.Alert.vAlertresolutionstate (NOLOCK) ARS
ON aa.AlertGuid = ARS.AlertGuid JOIN
OperationsManagerDW.[dbo].vRule (NOLOCK) Vr
ON aa.AlertProblemGuid =  Vr.RuleGuid JOIN OperationsManagerDW.dbo.vManagementPack (NOLOCK) Mp
ON vr.ManagementPackRowId =  mp.ManagementPackRowId
WHERE aa.Severity = 2 AND
ars.ResolutionState = 255
union
SELECT aa.AlertGuid AS [AlertId],aa.RaisedDateTime,
aa.Severity,
ars.ResolutionState,
CASE WHEN
 ars.ResolutionState < 254 THEN
 'Open' ELSE 'closed' END AS STATUS,
mp.ManagementPackSystemName AS [ManagementPack],
mp.ManagementPackVersionIndependentGuid AS [MPID]
FROM OperationsManagerDW.Alert.vAlert (NOLOCK) AA JOIN OperationsManagerDW.Alert.vAlertresolutionstate (NOLOCK) ARS
ON aa.AlertGuid = ARS.AlertGuid JOIN
OperationsManagerDW.[dbo].vMonitor (NOLOCK) Vm
ON aa.AlertProblemGuid =  Vm.MonitorGuid JOIN OperationsManagerDW.dbo.vManagementPack (NOLOCK) Mp
ON Vm.ManagementPackRowId =  mp.ManagementPackRowId
WHERE aa.Severity = 2 AND
 ars.ResolutionState = 255
) AS Base

SELECT bs.AlertId,bs.RaisedDateTime,
bs.ResolutionState,bs.Status,
bs.ManagementPack
FROM #Base_State BS
WHERE Bs.Status = 'Open'

 

Scom

How to find if there is a trace running in the server

What is Server-side tracing?
Server-side tracing is the process of having your SQL Server machine save events to a physical file on that machine without using the Profiler client tool.  Server-side tracing is enabled and controlled by using SQL Server system-supplied stored procedures and functions. With these system-supplied processes, you can identify what to trace, when to start and stop tracing, what traces are running, and view trace information stored in the trace file.

Here is how you view the number of traces currently in the system and the details of the trace:

Number of traces(active and stopped) currently in the system:
select * from sys.traces

Here is how you view the active(trace is running) traces in the system:

SELECT * FROM :: fn_trace_getinfo(default) WHERE property = 5 and value = 1

You can stop and terminate a trace by executing the following:

EXEC sp_trace_setstatus @traceid , @status = 0 — Stops the trace
EXEC sp_trace_setstatus @traceid , @status = 2 — Eliminates the definition of the trace from the server.

 

Find jobs that are least run or never run

I had to find all the jobs that either least run(one in 4 months) or never executed on all our SQL environments. i wrote the below script that pulls up that information.

SELECT Jb.Name AS [Job Name], CASE WHEN Jb.[Enabled] = 1 THEN ‘Yes’ ELSE ‘No’ END AS [Enabled], Jact.LastRun AS [Last Run Date], Jact.NextRun AS [Next Run Date]
FROM    msdb.dbo.sysjobs Jb
JOIN msdb.dbo.sysjobs_view Jv
ON Jb.Job_ID = Jv.Job_ID
JOIN
(
SELECT Job_ID, MAX(Last_Executed_Step_Date) LastRun, MAX(Next_Scheduled_Run_Date) NextRun
FROM    msdb.dbo.sysjobactivity
GROUP    BY Job_Id
) Jact
ON Jb.Job_ID = Jact.Job_ID
JOIN msdb.dbo.syscategories JC
ON Jb.Category_ID = JC.category_id
WHERE    DATEDIFF(day, ISNULL(LastRun, ‘1900-01-01’), GETDATE()) > 120
OR NextRun < GETDATE()

Snapshot of the outcome below.

Unused Jobs

Find all unused indexes in a database

While doing an index analysis activity i had to find the indexes are hardly being used in our production DB’s. I wrote this script to acheive the same

SELECT case when os.is_published = 1 then 'Replicated' else 'Not Replicated'
end as ReplicatedorNot,
OBJECT_NAME(idx.object_id) AS TableName ,
idx.name as Index_name,
istat.user_lookups,
istat.user_scans,
istat.user_seeks,
istat.user_updates
FROM sys.dm_db_index_usage_stats AS istat
JOIN sys.indexes AS idx ON idx.index_id = istat.index_id
join sys.objects os
on idx.object_id = os.object_id
AND idx.object_id = istat.object_id
WHERE
istat.database_id = DB_ID('databasename') -- type the name of the Db
and object_name(idx.object_id) not like 'Ms%'
and object_name(idx.object_id) not like 'sys%'
and object_name(idx.object_id) not like 'id_%'
and object_name(idx.object_id) not like 'conflict%'
and object_name(idx.object_id) not like 'Any other tables that you like to avoid%'
AND idx.is_unique_constraint = 0 -- no unique indexes
AND idx.is_primary_key = 0
AND idx.is_disabled = 0
AND idx.type > 1 -- don't consider heaps/clustered index
AND ( ( istat.user_seeks + istat.user_scans +istat.user_lookups )  <  istat.user_updates
or (istat.user_seeks = 0 AND istat.user_scans = 0 AND istat.user_lookups = 0))
order by (istat.user_seeks + istat.user_scans +
istat.user_lookups)

Primary Keys which are not Clustered Indexes in a database

Recently i was asked to find all the Primary keys that are not clustered Indexes in the database.I constructed the below script which will help us to locate the primary keys in the database which are not clustered index.

Use DatabaseName
go
SELECT idx.name AS IndexName
, fg.name AS Filegroup
, object_name(idx.object_id) AS TableName
, CASE
WHEN index_id = 1 THEN
‘Clustered’
WHEN index_id = 0 THEN
‘heap’
ELSE
‘Non Clustered’
END AS TypeOfIndex
, CASE
WHEN idx.is_primary_key = 1 AND index_id > 1 THEN
‘Primary key as Unique Non Clustered Index’
WHEN idx.is_primary_key = 1 AND index_id = 1 THEN
‘PK and Clustered’
ELSE
‘Unique Non-Cl’
END AS PrimaryKeyAsNonCLIndex
FROM
sys.filegroups fg
JOIN sys.indexes idx
ON fg.data_space_id = idx.data_space_id
JOIN sys.objects O
ON object_name(idx.object_id) = O.name
WHERE
object_name(idx.object_id) NOT LIKE ‘sys%’
AND idx.is_primary_key = 1
AND index_id > 1
ORDER BY
TableName

Find Increment Values and Current Identity value of all tables in a database

Use DatabaseName
Go
SELECT IDENT_SEED(TABLE_NAME) AS SeedValue,
IDENT_INCR(TABLE_NAME) AS IncrementValue,
IDENT_CURRENT(TABLE_NAME) AS Current_Identity_Value,
TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), ‘TableHasIdentity’) = 1
AND TABLE_TYPE = ‘BASE TABLE’

Find all the Foriegn Key’s and their details in a database

The below script helps us to find all the foreign keys in a database and there details. i have used AdventureWorks2012 db as example.

USE AdventureWorks2012
go
SELECT fk.name AS ForeignKey,
IsNotForReplication = case when fk.is_not_for_replication = 0 then ‘No’ else ‘yes’
end,
OBJECT_NAME(fk.parent_object_id) AS TableName,
COL_NAME(fkc.parent_object_id,
fkc.parent_column_id) AS ColumnName,
OBJECT_NAME (fk.referenced_object_id) AS ReferenceTableName,
COL_NAME(fkc.referenced_object_id,
fkc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fkc
ON fk.OBJECT_ID = fkc.constraint_object_id
order by IsNotForReplication

A snapshot of the outcome below:

FK Details

Replication – Script – Dropping a table from an existing publication in SQL Server

In this article I will share the script that I use while dropping a table from an existing publication.
Below is the script that helps us achieve the same.

use [databasename]--Name of the DB in replication
go
exec sp_dropsubscription @publication=N'NameOfPublication',
@article=N'TestTable',--name of the table to be dropped
@subscriber=N'all'
go
use [databasename]
go
exec sp_droparticle @publication = N'NameOfPublication',
@article = N'TestTable',--name of the table to be dropped
@force_invalidate_snapshot = 1
go

Technical reference:
The details for the stored procedure sp_dropsubscription can be found in the below link
http://technet.microsoft.com/en-us/library/ms184385.aspx

Replication – Script – How to add a table to an existing publication in SQL Server

In this article i will share the script that I use when I add a table to an existing publication in SQL Server.
The below script would help us to add any table to an existing publication. The most important thing about adding a table to replication is that the table must have a primary key.

Below is the TSQL script to achieve the same.

use [databasename]--Name of the table involved in Replication
Go
exec sp_addarticle @publication = N'NameOfPublication',
@article = N'TestTable', @source_owner = N'dbo',
@source_object = N'TestTable', 
@type = N'logbased',@description = null,
@creation_script = null,
@pre_creation_cmd = N'drop', @schema_option = 0x0000000008035DDB,
@force_invalidate_snapshot = 1,
@identityrangemanagementoption = N'manual',
@destination_table = N'TestTable',
@destination_owner = N'dbo', @status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_MSins_dboTestTable',
@del_cmd = N'CALL sp_MSdel_dboTestTable',
@upd_cmd = N'SCALL sp_MSupd_dboTestTable'
Go

More details on the used stored procedure [sp_addarticle] can be found on the below mentioned technet article

http://technet.microsoft.com/en-us/library/ms173857.aspx

%d bloggers like this: