获取行数和百分比 [英] Get row counts and percentages

问题描述

大家好，

我有一个我在网上找到的sql来获取表和行计数列表。我有70张桌子。所有表格都有一列"有效"。表示记录是否处于活动状态，具有结束日期。这就是我要找的。所有表格的列表
和行数。活动记录的百分比，非活动记录的百分比以及过去有多远的记录百分比。我在网上找到的当前sql如下。提前致谢

I have a sql that i found online to get the list of tables and row counts. I have 70 tables. All the tables have a column "Active" which indicates if the record is Active or not with an end date. Here is what i am looking for. List of all the Tables and Row counts. Percentage of Active records, percentage of non active records and percentage of records how far in the the past have ENDDATES. The current sql i have that i found online is below. Thanks in advance

sELECT 
    TableName = t.NAME,
    TableSchema = s.Name,
    RowCounts = p.rows
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
WHERE 
    t.is_ms_shipped = 0
	and s.name='dbo'
	
GROUP BY
    t.NAME, s.Name, p.Rows
ORDER BY   s.Name, t.Name

推荐答案

希望这个示例能让你开始：

Hope this sample get you start:

SET NOCOUNT ON;
   DECLARE @TABLE_NAME NVARCHAR(50),
        @TABLE_SCHEMA NVARCHAR(50) = 'dbo',
        @sql          NVARCHAR(4000),
        @col          NVARCHAR(50)='Active'
CREATE TABLE #t
  (
     id      INT IDENTITY(1, 1),
     TableName VARCHAR(50),
     [Active%] DECIMAL(8, 2),
	 totalRows int
  )
DECLARE c_cursor CURSOR FOR
  SELECT distinct [TABLE_NAME]
  FROM   [INFORMATION_SCHEMA].[COLUMNS]
  WHERE  TABLE_SCHEMA = @TABLE_SCHEMA and [COLUMN_NAME]='Active'
         --AND IS_Nullable = 'YES'
OPEN c_cursor;
FETCH NEXT FROM c_cursor INTO @TABLE_NAME;
WHILE ( @@FETCH_STATUS = 0 )
  BEGIN
      SET @sql = N' INSERT INTO #t (TableName, [ACtive%],totalRows)
        SELECT TOP 1 ''' + @TABLE_NAME
                 + ''' , (Sum(case when active=1 then 1 else null end) Over() *1.0)/COUNT(*) Over() *100.0 as [Active%]
				 ,COUNT(*) Over() totalRows FROM '
                 + Quotename(@TABLE_SCHEMA) + '.'
                 + Quotename( @TABLE_NAME)
      -- print @sql
      EXEC (@sql);
      FETCH NEXT FROM c_cursor INTO @TABLE_NAME;
  END
CLOSE c_cursor;
DEALLOCATE c_cursor;


SELECT TableName,  [Active%], totalRows FROM #t

DROP TABLE #t

这篇关于获取行数和百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！