SQL Server – Discover tables that are replicated

Here are some useful SQL Scripts to get the details of what tables are included in replication in the database the query is connected to.

SELECT		*
FROM		sys.tables
WHERE		is_replicated = 1
ORDER BY	name

The script below will provide extra details on the subscriptions.

SELECT		publisher=sysservers.srvname, 
		subscriber_db=dest_db, 
		Subscriber=syssubscriptions.srvname, 
		article_name=sysarticles.name,
		syspublications.name AS PublicationName
FROM		syssubscriptions 
JOIN		sysarticles 
ON		syssubscriptions.artid=sysarticles.artid
JOIN		master.dbo.sysservers 
ON		syssubscriptions.srvid =sysservers.srvid
JOIN		syspublications 
ON		syspublications.PubId = sysarticles.pubid
ORDER BY	article_name

Additionally you could use the query below to find replication details for a specific table. The example is checking the Customer table

SELECT		P.name Publication,
		A.name TableName,
		A.dest_table DestinationTable 
FROM		syspublications P 
INNER JOIN	sysarticles A ON P.pubid = A.pubid
WHERE		A.name ='Customer'

Creating User-Defined Table Types in SQL Server

Here is a script that will create a user-defined table type in SQL Server. I was using 2008 R2.

In my type I am adding a primary key (Sku) column and a CHECK constraint on the Quantity column to state that the value must be greater than zero.

CREATE TYPE [dbo].[SkuListWithQuantity] AS TABLE(
[Sku] [CHAR](64) NOT NULL,
[Quantity] [INT] NOT NULL CHECK (Quantity > 0),
PRIMARY KEY CLUSTERED ([Sku] ASC)
);

You can test it works for valid values using the following SQL:

DECLARE @Skus SkuListWithQuantity
INSERT INTO @Skus (Sku, Quantity) VALUES ('FBIUN140095', 21), ('KLIJJ146314', 10)
SELECT * FROM @Skus


You can test it with an invalid Quantity value using the following SQL:

DECLARE @Skus SkuListWithQuantity
INSERT INTO @Skus (Sku, Quantity) VALUES ('FBIUN140095', 0), ('FBIUN140095', 10)
SELECT * FROM @Skus

You will receive an error like:

Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint “CK__#5165187F__Quant__52593CB8”. The conflict occurred in database “tempdb”, table “@Skus”.
The statement has been terminated.

You can test it with a duplicate Primary Key value using the following SQL:

DECLARE @Skus SkuListWithQuantity
INSERT INTO @Skus (Sku, Quantity) VALUES ('FBIUN140095', 21), ('FBIUN140095', 10)
SELECT * FROM @Skus

You will receive an error like:

Msg 2627, Level 14, State 1, Line 4
Violation of PRIMARY KEY constraint ‘PK__#5CD6CB2__CA1FD3C45EBF139D’. Cannot insert duplicate key in object ‘dbo.@Skus’. The duplicate key value is (FBIUN140095 ).
The statement has been terminated.

Inserting data taken from a Binary column back into the Database in C#

I had to write some C# code to insert data into a BINARY(16) column in a SQL Server database table as part of a setup task for a test I had to write.  I knew the Binary value I wanted to write as it existed in a column in the table already, which was the value 0xE4D174253E281E7BE02005183E72F136.  Here is the code to put this value back into a byte[] variable that is passed in a parameter which is written to the database:

var vector = new byte[] { 0xE4, 0xD1, 0x74, 0x25, 0x3E, 0x28, 0x1E, 0x7B, 0xE0, 0x20, 0x05, 0x18, 0x3E, 0x72, 0xF1, 0x36 };