Contare il numero di righe in più tabelle in una query

Ho un SQL Server 2005 database che memorizza i dati per più utenti. Ogni tabella che contiene proprietà dell’utente di dati di una colonna denominata OwnerID che identifica il proprietario; la maggior parte ma non tutti i tavoli hanno questa colonna.

Voglio essere in grado di contare il numero di righe ‘di proprieta’ di un utente in ogni tabella. In altre parole, voglio una query che restituisce i nomi di ogni tabella che contiene un OwnerID colonna, e conta il numero di righe di ogni tabella che corrispondono a un dato OwnerID valore.

Posso tornare solo i nomi dei corrispondenti tabelle utilizzando questa query:

SELECT OBJECT_NAME(object_id) [Table] FROM sys.columns 
    WHERE name = 'OwnerID' ORDER BY OBJECT_NAME(object_id);

Che la query restituisce un elenco di nomi di tabella come questa:

+---------+
|  Table  |
+---------+
| Alpha   |
| Beta    |
| Gamma   |
| ...     |
+---------+

Ma è possibile scrivere una query che può anche contare il numero di righe di ogni tabella che corrispondono a un dato OwnerID? vale a dire:

+---------+------------+
|  Table  |  RowCount  |
+---------+------------+
| Alpha   | 2042       |
| Beta    | 49         |
| Gamma   | 740        |
| ...     | ...        |
+---------+------------+

Nota: L’elenco dei nomi di tabella deve essere restituito in modo dinamico, non è adatto per codificare i nomi di tabella nella query.


Edit: la risposta…

(Non posso modificare le vostre risposte, ma posso modificare la mia domanda, quindi la metto qui…)

Damien_The_Unbeliever era essenzialmente la risposta corretta, ma SQL Server non consente di concatenazione di stringhe in un exec affermazione così ho dovuto impostare la query prima di exec istruzione. La query finale è il seguente:

DECLARE @OwnerID int;
SET @OwnerID = 1;

DECLARE @ForEachSQL varchar(100);
SET @ForEachSQL = 'INSERT INTO #t(TableName,RowsOwned) SELECT ''?'', COUNT(*) FROM ? WHERE OwnerID = ' + CONVERT(varchar(11), @OwnerID);

CREATE TABLE #t(TableName sysname, RowsOwned int);
EXEC sp_MSforeachtable @ForEachSQL, 
    @whereAnd = 'AND o.id IN (SELECT id FROM syscolumns where name=''OwnerID'')';
SELECT * FROM #t ORDER BY TableName;
DROP TABLE #t;

OriginaleL’autore gutch | 2010-11-15

4 Replies
  1. 4

    È possibile utilizzare sp_MSForeachtable, e l’ @whereand parametro per specificare un filtro in modo che si sta solo lavorando contro di tabelle con un OwnerID colonna. Creare una tabella temporanea e compilare per ogni tabella di corrispondenza. Qualcosa come:

    create table #t(tablename sysname,Cnt int)
    sp_MSforeachtable 'insert into #t(tablename,Cnt) select ''?'',COUNT(*) from ?',@whereAnd='and o.id in (select id from syscolumns where name=''OwnerID'')'
    select * from #t

    Due principali avvertenze ricordare, prima è che sp_MSforeachtable è “senza documenti”, in modo da utilizzare a proprio rischio – potrebbe essere improvvisamente rimosso da SQL Server da qualsiasi tipo di manutenzione o nella prossima release.

    La seconda è che, avendo uno schema dinamico è di solito un segno che qualcosa è andato storto nella modellazione, possibilmente un attributo di scissione (dove le vendite per il mese di gennaio e febbraio sono dati di tabelle diverse, anche se sono logicamente la stessa cosa e deve apparire nella stessa tabella, possibilmente con una colonna aggiuntiva per distinguerli)


    E, naturalmente, si desidera filtrare in base a un particolare clientID, in modo che la query sarebbe più simile a:

    'insert into #t(tablename,Cnt) select ''?'',COUNT(*) from ? where OwnerID=' + @OwnerID

    (Supponendo @OwnerID è il proprietario ha cercato, ed è un int)

    Inoltre, se ci sono tabelle padre e figlio, la somma delle righe sarà ancora meno significativa statistica di quanto già non sia.
    mi dispiace, si accostò, rotolò la tua modifica, dal momento che sia curato allo stesso tempo. Da allora ri-aggiunto il link per “funzioni nascoste” – è che l’unica modifica che hai fatto?
    sì, tutto qui, grazie
    Hai ragione su uno schema dinamico che di solito è un segno di modellazione è andato storto… ma in questo caso credo che stiamo usando il potere per fare il bene! Questo script deve essere eseguito come parte del test automatico per determinare se il codice ha cambiato tutti i dati che non dovrebbe; il conteggio delle righe sarà controllato prima e dopo la corsa e non deve cambiare. La dinamica della tabella di ricerca significa che il test verrà automaticamente coprire nuove tabelle senza qualcuno a ricordare di aggiornare il test.

    OriginaleL’autore Damien_The_Unbeliever

  2. 1

    Questo potrebbe ottenere le informazioni da sysindexes. Può essere un po ‘ fuori di data, ma vi darà un conteggio approssimativo

    SELECT 
        [TableName] = so.name, 
        [RowCount] = MAX(si.rows) 
    FROM 
        sysobjects so, 
        sysindexes si 
    WHERE 
        so.xtype = 'U' 
        AND 
        si.id = OBJECT_ID(so.name) 
    GROUP BY 
        so.name 
    ORDER BY 
        2 DESC

    Se si aveva bisogno di essere al 100% quindi è possibile utilizzare le funzionalità non documentate sp_MSForEachTable

    DECLARE @SQL VARCHAR(255) 
        SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')' 
        EXEC(@SQL) 
    
        CREATE TABLE #foo 
        ( 
            tablename VARCHAR(255), 
            rc INT 
        ) 
    
        INSERT #foo 
            EXEC sp_msForEachTable 
                'SELECT PARSENAME(''?'', 1), 
                COUNT(*) FROM ?' 
    
        SELECT tablename, rc 
            FROM #foo 
            ORDER BY rc DESC 
    
        DROP TABLE #foo 
    +1 per PARSENAME(''?'', 1) — dà un output molto più semplice rispetto a quello standard ''?''

    OriginaleL’autore Kevin Ross

  3. 1

    È possibile utilizzare questo:

    DECLARE @nSQL NVARCHAR(MAX)
    
    SELECT @nSQL = COALESCE(@nSQL + 'UNION ALL ' + CHAR(10), '') 
        + 'SELECT ''' + TABLE_NAME + ''' AS TableName, COUNT(*) FROM ' + QUOTENAME(TABLE_NAME) + CHAR(10)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = 'strKey'
    
    -- This will PRINT out the dynamically generated SQL statement. Just replace this with EXECUTE(@nSQL) when you are happy to run it.
    PRINT @nSQL

    Aggiornamento: Alla ricerca di una specifica OwnerId:

    DECLARE @nSQL NVARCHAR(MAX)
    DECLARE @OwnerId INTEGER
    SET @OwnerId = 1
    
    SELECT @nSQL = COALESCE(@nSQL + 'UNION ALL ' + CHAR(10), '') 
            + 'SELECT ''' + TABLE_NAME + ''' AS TableName, COUNT(*) FROM ' + QUOTENAME(TABLE_NAME) + ' WHERE OwnerId = @OwnerId' + CHAR(10)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = 'strKey'
    
    EXECUTE sp_executesql @nSQL, '@OwnerId INTEGER', @OwnerId
    Grazie, questa risposta ha lavorato bene, ma ha bisogno di essere modificato per limitare solo le righe corrispondenti al mio desiderato OwnerID
    per completare la mia risposta, ho aggiornato aggiungere la possibilità di farlo 🙂

    OriginaleL’autore AdaTheDev

  4. 0
    SELECT 
        O.ID,
        O.NAME,
        I.ROWCNT 
    FROM SYSOBJECTS O 
    INNER JOIN SYSINDEXES I 
    ON O.ID = I.ID 
    WHERE O.UID = 5 
        AND O.XTYPE = 'U' 
        AND I.STATUS = 0

    Provare a utilizzare questa query ti darà id della tabella, il nome della tabella e non di righe per tabella.

    UID = 5 significa che il check-in particolare schema che ha id = 5.È possibile controllare schema id utilizzando SELECT SCHEMA_ID('<schema name>');

    XTYPE = ‘U’ significa definito dall’Utente solo le tabelle.

    OriginaleL’autore Sukhjant Singh

Lascia un commento