How many times have you copied objects from database A to B, and needed to compare object counts when complete? Or, maybe you just want to see what's in one database that is not in another? Here's a quick piece to give you object type counts in your database, and another to show you what's in one database that's not in the other.
--
object type counts
SELECT
'Count' = COUNT(*),
s.Type,
'Description' = CASE [Type]
WHEN
'AF' THEN 'Aggregate function (CLR)'
WHEN
'C' THEN 'CHECK Constraint'
WHEN
'D' THEN 'DEFAULT (constraint or
stand-alone)'
WHEN
'EC' THEN 'Edge Constraint'
WHEN
'F' THEN 'FOREIGN KEY Constraint'
WHEN
'FN' THEN 'Scalar functions'
WHEN
'IF' THEN 'SQL Inline Table-valued
Function'
WHEN
'IT' THEN 'Internal table'
WHEN
'P' THEN 'SQL Stored Procedure'
WHEN
'PK' THEN 'Primary Key'
WHEN
'R' THEN 'Rule (old-style, stand-alone)'
WHEN
'RF' THEN 'Replication-filter procedure'
WHEN
'S' THEN 'System base table'
WHEN
'SN' THEN 'Synonym'
WHEN
'SO' THEN 'Sequence Object'
WHEN
'SQ' then 'Service Queue'
WHEN
'TA' THEN 'Assembly (CLR) DML trigger'
WHEN
'TF' THEN 'SQL table-valued-function'
WHEN
'TR' THEN 'SQL DML trigger'
WHEN
'TT' THEN 'Table type'
WHEN
'UQ' THEN 'UNIQUE Constraint'
WHEN
'U' THEN 'User Table'
WHEN
'V' THEN 'View'
WHEN
'X' THEN 'Extended stored procedure'
ELSE
type END
FROM
sys.objects s
GROUP BY
s.type
ORDER BY
s.type
And this piece will tell you what tables are in the database you are connected to, that is not in the 'DatabaeName' on the other 'ServerName' in the query:
SELECT a.name FROM sys.objects a
WHERE a.type = 'U'
AND NOT EXISTS(
SELECT 1 FROM ServerName.DatabaseName.sys.objects b
WHERE b.type = 'U'
AND a.name = b.name
)
Of course, you can change it to any object type, or even all objects... and you'll need to have a linked server for 'ServerName' in order for it to work.
Hope it helps!
No comments:
Post a Comment