Finding keyless tables across multiple servers
Christina Leo (@christinaleo) asked this question on twitter today:
So, if I ever needed a POSH script, today is the day. Having to inventory all prod databases to find tables missing primary keys.
I have a query that returns what I need, but I need a report for each of 60+ databases across 3 servers
I haven't joined the cool kids' club yet, so my first instinct is still not to do this with PowerShell. Maybe it would be a simpler task there, but it's just not how I think.
We know that, for an individual database, we can get the keyless tables with the following query:
SELECT [table] = QUOTENAME(s.name) + '.' + QUOTENAME(t.name) FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] LEFT OUTER JOIN sys.key_constraints AS k ON t.[object_id] = k.parent_object_id AND k.[type] = 'PK' WHERE k.[object_id] IS NULL;
And to get it across all the databases on a server, you could simply pass that query to sp_msforeachdb. That was @BradHarker's first response (which he quickly posted on his blog – albeit without a PoSH script as the title of the post suggests). I reminded him that the undocumented and unsupported sp_msforeachdb can skip databases, which I've complained about before and even created a replacement. In either case though, in order to run it across multiple servers, you'd still have to create a new query for each server, and then manually merge the results. In Christina's case (three servers) that's not a very bleak prospect, but what if you have 30 servers? 50? More?
Let's pretend you have a linked server setup for each of the additional servers where you'd like to run the same query. Not that far-fetched, right? If you have access to all of the servers from a single location, why not use some fun dynamic SQL and save yourself some merging? As I said before, this isn't exactly less complex than an equivalent PowerShell script, but it's still one way to do it.
CREATE TABLE #src ( s NVARCHAR(130), db NVARCHAR(130) ); DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N' UNION ALL SELECT s = ''' + n + ''', db = QUOTENAME(name) FROM ' + n + '.master.sys.databases WHERE state = 0 AND database_id > 4' FROM ( SELECT n = '' UNION ALL SELECT QUOTENAME(name) FROM sys.servers WHERE server_id > 0 /* AND (name IN ('a','b')) */ -- add filtering here if desired ) AS x; SET @sql = STUFF(@sql, 1, 13, ''); INSERT #src(s,db) EXEC sys.sp_executesql @sql; SET @sql = N''; /* COLLATE clause is important if you have mixed collation databases */ SELECT @sql += REPLACE(N' UNION ALL SELECT [source] = ''$src$'', [table] = QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) COLLATE SQL_Latin1_General_CP1_CI_AS FROM $src$.sys.tables AS t INNER JOIN $src$.sys.schemas AS s ON t.[schema_id] = s.[schema_id] LEFT OUTER JOIN $src$.sys.key_constraints AS k ON t.[object_id] = k.parent_object_id AND k.[type] = ''PK'' WHERE k.[object_id] IS NULL', '$src$', COALESCE(NULLIF(s + '.', '.'), '') + db ) FROM #src; SET @sql = STUFF(@sql, 1, 13, ''); EXEC sys.sp_executesql @sql; DROP TABLE #src;
Probably not the prettiest script you've ever seen, and relies on connectivity (and permissions) to those other instances via linked servers. But it's definitely one way to skin the cat.