October 20, 2011 | SQL Server

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 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.
 

12 comments on this post

    • Argenis Fernandez - October 20, 2011, 9:18 PM

      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'

    • AaronBertrand - October 20, 2011, 9:26 PM

      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.

    • bender - October 20, 2011, 9:53 PM

      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
      }

    • bender - October 20, 2011, 10:04 PM

      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
      }

    • Henk - October 20, 2011, 10:20 PM

      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"

    • Randall Utt - October 20, 2011, 10:43 PM

      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?

    • Aaron Bertrand - October 20, 2011, 10:45 PM

      Henk, that depends. Did you add/remove carriage returns/line feeds around UNION ALL? I did test the script.

    • AaronBertrand - October 20, 2011, 11:02 PM

      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.

    • drsql - October 20, 2011, 11:24 PM

      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

    • AaronBertrand - October 20, 2011, 11:29 PM

      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.

    • drsql - October 24, 2011, 10:12 PM

      Very true, my bad 🙂

    • Syed Sami - November 30, 2011, 12:33 PM

      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

Comments are closed.