Christina Leo 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.
I dont know how good this query is but I used to follow this query to find the tables without PK.
SELECT OBJECT_NAME(I.[object_id]) AS TableName,I.[type_desc] KeyIndex FROM
sys.indexes I
INNER JOIN sys.tables T ON T.[object_id] = I.[object_id]
WHERE I.name = 'NC' OR I.name IS NULL
Very true, my bad 🙂
Thanks Louis, but like OBJECTPROPERTY(), you can't do that remotely. I prefer the joins because they continue work with the only change being a prefix.
You probably know about this (but if not, or one of the readers, it is helpful at times), but you can use OBJECT_SCHEMA_NAME to get the schema name of an object:
SELECT QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(t.name) as SchemaQualifiedName
FROM sys.tables AS t
Randall, no reason really except that this was the method that struck me first. I prefer things that are scriptable, repeatable, and source-control-able. I don't use CMS personally and I know that many people don't have access to certain 3rd party tools. I didn't say that my way was better, I just said that this is one way to do it.
Henk, that depends. Did you add/remove carriage returns/line feeds around UNION ALL? I did test the script.
Why can't you use Central Management Servers Option under Registered Servers to create a group containing the servers you want to query…right click on the group which gives you a new Query window that will run the script against all the servers in that group and output the results already concantenated? Or use a tool from a 3-rd party vendor and do basically the same thing? Just curious?
Knitpicking:
SET @sql = STUFF(@sql, 1, 12, ");
should be
SET @sql = STUFF(@sql, 1, 13, ");
otherwise you'll get an error ("Unknown stored procedure L"
And…I just realized she was asking for Primary keys, not clustered indexes. Here is one that does it:
function FindTablesNoClustIdx([string]$ServerName)
{
sl SQLSERVER:\SQL\$ServerName\DATABASES
$databases = gci
foreach($database in $databases)
{
$tables = $database.Tables
foreach($table in $tables)
{
$hasPK = $false
foreach($index in $table.Indexes)
{
if($index.IndexKeyType -eq [Microsoft.SqlServer.Management.SMO.IndexKeyType]::DriPrimaryKey)
{
$hasPK = $true
}
}
if($hasPK -eq $false)
{
Write-Host "Table $Table.Name in database $database.name on server $ServerName has no primary key index"
}
}
}
}
$smo = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
cls
$serverFile = "C:\Test\Servers.txt"
$servers = Get-Content $serverFile
foreach($server in $servers)
{
FindTablesNoClustIdx $server
}
Easier that you think using powershell. Just make sure your entries in the servers.txt file list the default instance name as DEFAULT if the sql is a default install (SERVER\DEFAULT). This just writes out the table names that have no clustered index to the host, but it's a start. I just whipped it up right quick.
function FindTablesNoClustIdx([string]$ServerName)
{
sl SQLSERVER:\SQL\$ServerName\DATABASES
$databases = gci
foreach($database in $databases)
{
$tables = $database.Tables
foreach($table in $tables)
{
if($table.HasClusteredIndex -ne $true)
{
Write-Host "$table.Name has no clustered index"
}
}
}
}
$smo = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
cls
$serverFile = "C:\Test\Servers.txt"
$servers = Get-Content $serverFile
foreach($server in $servers)
{
FindTablesNoClustIdx $server
}
Thanks Argenis, however the goal of my script was to use a query that would easily transfer across databases and across servers without too much modification. Your suggestion doesn't include schema, for example, which can be important, and you can't call OBJECTPROPERTY() that way outside of the right context.
I think this is a little easier 🙂
SELECT so.name AS TableName, OBJECTPROPERTY(so.object_id, 'TableHasPrimaryKey') AS HasPrimaryKey
FROM sys.objects so
WHERE type = 'U'