This question is about SQL Server replication.
I am running SQL Server 2019 v15.0.4420.2 and I get a large number of replicated tables from outside the organisation.
What I am looking for is a SQL query, if possible, on the system tables, that can tell me what tables exist and when they were last refreshed/updated/synchronised. They have a specific naming convention, being prefixed with an underscore.
I asked a DBA colleague and they gave me a list, based on the indexes. I'm no expert, and I don't have system level access, but I don't think indexes are a rock solid proxy for this query. Not all tables are indexed.
SELECT DISTINCT
SCHEMA_NAME(o.schema_id) AS SchemaName,
o.name AS TableName,
o.create_date,
o.modify_date,
DATEDIFF(DAY, o.modify_date, GETDATE()) AS Age,
--CAST(DATEDIFF(DAY, o.modify_date, GETDATE()) / 7 AS VARCHAR) AS Weeks,
CASE
WHEN DATEDIFF(DAY, o.modify_date, GETDATE()) > 7
THEN 'Out of Synch by ' + CAST(DATEDIFF(DAY, o.modify_date, GETDATE()) / 7 AS VARCHAR) + ' Weeks'
ELSE 'Synched'
END AS Evaluation
FROM
sys.objects o
JOIN
INFORMATION_SCHEMA.COLUMNS c ON o.name = c.TABLE_NAME
AND SCHEMA_NAME(o.schema_id) = c.TABLE_SCHEMA
WHERE
SCHEMA_NAME(o.schema_id) = 'dbo'
AND o.name LIKE '[_]%'
ORDER BY
Age DESC;
Is there an easy solution to this?
One key part of the problem is that I do not know what "modified date" means when applied to replication.
EDIT: Discovered this reference: modify_date datetime Date the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when an index on the table or view is created or altered.
EDIT 2: It would seem that @CharlieFace recommendation sp_replmonitorhelpsubscription would be exactly what I would be looking for. Is there any way that I as a subscriber, and only a business user (not a sys Admin) could make use of that? Or would I have to ask the publisher for access of some sort to access this sp? Is it a admin level only? OR would it be possible to assign permissions to a read only user to do this?
_character is a wildcard inLIKEexpressions. If your names start with underscore you probably wantLIKE '[_]%'msdbdatabase. Distribution refers to the go-between DB, you should learn up the terminology and then the table setup might be clear to you.sp_replmonitorhelpsubscriptionrather than querying those tables yourself learn.microsoft.com/en-us/sql/relational-databases/…