0

I need to query all databases, tables, columns, and number of rows for each table from a server.

The following code almost does what I need except it is only for a single database. I need this output with the addition of a column for the database name. And for it to run against all databases instead of just a single named one. Also need the number of records for each table

USE [temp_db]; 

SELECT 
    OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],   
    T.[name] AS [table_name], AC.[name] AS [column_name],   
    TY.[name] AS system_data_type, AC.[max_length],  
    AC.[precision], AC.[scale], AC.[is_nullable], AC.[is_ansi_padded]  
FROM
    sys.[tables] AS T   
INNER JOIN 
    sys.[all_columns] AC ON T.[object_id] = AC.[object_id]  
INNER JOIN 
    sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id] 
                   AND AC.[user_type_id] = TY.[user_type_id]   
WHERE 
    T.[is_ms_shipped] = 0  
ORDER BY 
    T.[name], AC.[column_id]

Current output:

Schema|table_name|column_name|system_data_type|max_length|precision|scale|is_nullable|is_ansi_padded

I need the output to be:

db_name|table_name|column_name|system_data_type|num_records
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Seth
  • 99
  • 1
  • 1
  • 4
  • You'll need to use dynamic SQL for these. One method would be by using a `CURSOR` and `sys.databases` (yes, this is one of those times where using a loop set method is the right choice). – Thom A May 04 '19 at 21:50
  • Possible duplicate of [How to run the same query on all the databases on an instance?](https://stackoverflow.com/questions/18462410/how-to-run-the-same-query-on-all-the-databases-on-an-instance) – SMor May 05 '19 at 11:26

0 Answers0