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 wrote a utility procedure as 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 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 EXEC 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 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.