List all databases, tables, and views on a server

Question

Using SQL Server Management Studio 18, I'm trying to modify the below query to return all tables and views for each database on the server, along with the row count and column count for each table and view.

The code below works well but does not include views or the column count & row count for each view and table. Ideally, there would be another column that identifies the object as a view or a table.

Columns:

ServerName ; DBName ; SchemaName ; Object (View or Table) ; ObjectName ; RowCount ; ColumnCount

Code:

SET NOCOUNT ON

DECLARE @AllTables TABLE
        (
         ServerName NVARCHAR(200)
        ,DBName NVARCHAR(200)
        ,SchemaName NVARCHAR(200)
        ,TableName NVARCHAR(200)
        )
DECLARE @SearchSvr NVARCHAR(200)
       ,@SearchDB NVARCHAR(200)
       ,@SearchS NVARCHAR(200)
       ,@SearchTbl NVARCHAR(200)
       ,@SQL NVARCHAR(4000)

SET @SearchSvr = NULL  --Search for Servers, NULL for all Servers
SET @SearchDB = NULL  --Search for DB, NULL for all Databases
SET @SearchS = NULL  --Search for Schemas, NULL for all Schemas
SET @SearchTbl = NULL  --Search for Tables, NULL for all Tables

SET @SQL = 'SELECT @@SERVERNAME
        ,''?''
        ,s.name
        ,t.name
         FROM [?].sys.tables t 
         JOIN sys.schemas s on t.schema_id=s.schema_id 
         WHERE @@SERVERNAME LIKE ''%' + ISNULL(@SearchSvr, '') + '%''
         AND ''?'' LIKE ''%' + ISNULL(@SearchDB, '') + '%''
         AND s.name LIKE ''%' + ISNULL(@SearchS, '') + '%''
         AND t.name LIKE ''%' + ISNULL(@SearchTbl, '') + '%''
      -- AND ''?'' NOT IN (''master'',''model'',''msdb'',''tempdb'',''SSISDB'')
           '
-- Remove the '--' from the last statement in the WHERE clause to exclude system tables

INSERT  INTO @AllTables
        (
         ServerName
        ,DBName
        ,SchemaName
        ,TableName
        )
        EXEC sp_MSforeachdb @SQL
SET NOCOUNT OFF
SELECT  *
FROM    @AllTables
ORDER BY 1,2,3,4

Answer 1

Here's an example you can play with that addresses several of the things mentioned in the comments (and a few others):

DECLARE @SearchSvr nvarchar(128) = NULL  -- NULL for all Servers
       ,@SearchDB  nvarchar(128) = NULL  -- NULL for all Databases
       ,@SearchS   nvarchar(128) = NULL  -- NULL for all Schemas
       ,@SearchTbl nvarchar(128) = NULL; -- NULL for all Tables

  DECLARE @t TABLE 
  (
     DBName      sysname
    ,SchemaName  sysname
    ,ObjectName  sysname
    ,ObjectType  varchar(5)
    ,[RowCount]  bigint
    ,ColumnCount int 
  );

  DECLARE @dbname  sysname
         ,@context nvarchar(1000)
         ,@sql     nvarchar(max) = N'SELECT DBName = DB_NAME(),
                SchemaName  = s.name,
                ObjectName  = t.name,
                ObjectType  = CASE t.type 
                              WHEN ''U'' THEN ''Table'' 
                              ELSE ''View'' END,
                [RowCount]  = ps.rc,
                ColumnCount = c.cc
           FROM sys.objects AS t 
           INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id 
           OUTER APPLY
           (
             SELECT ps.object_id, rc = SUM(ps.row_count)
             FROM sys.dm_db_partition_stats AS ps
             WHERE ps.object_id = t.object_id AND index_id IN (0,1)
             GROUP BY ps.object_id
           ) AS ps
           OUTER APPLY
           (
             SELECT c.object_id, cc = COUNT(*)
             FROM sys.columns AS c WHERE c.object_id = t.object_id
             GROUP BY c.object_id
           ) AS c
           WHERE s.name LIKE @s AND t.name LIKE @t 
             AND t.type IN (''U'', ''V'');';

IF @@SERVERNAME LIKE COALESCE(@SearchSvr, N'%')
BEGIN
  DECLARE @db cursor;

  SELECT @SearchS   = COALESCE(N'%' + @SearchS   + N'%', N'%'),
         @SearchTbl = COALESCE(N'%' + @SearchTbl + N'%', N'%');

  SET @db = cursor LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR 
    SELECT name FROM sys.databases WHERE state = 0 
    -- AND name NOT IN (N'master', N'model', N'msdb', N'tempdb', N'SSISDB')
       AND name LIKE COALESCE(@SearchDB, N'%');

  OPEN @db; FETCH NEXT FROM @db INTO @dbname;

  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @context = QUOTENAME(@dbname) + N'.sys.sp_executesql';

    INSERT @t(DBName,SchemaName,ObjectName,ObjectType,[RowCount],ColumnCount)
      EXEC @context @SQL, N'@s sysname, @t sysname', @SearchS, @SearchTbl;

    FETCH NEXT FROM @db INTO @dbname;
  END
END

SELECT ServerName = @@SERVERNAME, * FROM @t 
  ORDER BY DBName, SchemaName, ObjectName;

Notably:

• This will return row counts (without doing it the hard way) from tables and indexed views. Not sure if row count from a non-indexed view makes any sense, since those rows aren't stored anywhere.
• I don't bother storing @@SERVERNAME on every row in the table because that can't possibly change during execution. I did add an IF to make sure we don't bother doing anything if we're not on the server we thought.
• It uses a trick with EXEC (described here) to execute inside the context of each database, which means no ugly ''?'' and you don't need to worry about missing a prefix anywhere (you had JOIN sys.schemas but it should have been JOIN [?].sys.schemas).
• Don't use sp_MSForEachDB - it is undocumented, unsupported, and horribly broken (refs here, here, here, here). This code could have been shorter if using the replacement procedure I've written (sp_ineachdb, part of Brent Ozar's First Responder Kit), but then it abstracts away any learning.
• Unlike the linked answer, this will handle partitioned tables properly (just one row per table).
• Always use the N prefix on string literals that represent entity names or dynamic SQL - this ensures things won't break if someone names an object .
• Note that manually adding [square brackets] is not a safe way to protect you from SQL injection, nor is concatenating user input (which should always be considered a weapon). Did you try setting @SearchTbl to N'CEO''s notes', for example? Much more info about that and dynamic SQL in general in these links.
• Don't order by ordinal number - this is very brittle especially with table variables where you may frequently add/change columns.