Sqlserver统计当前库有多少表

统计当前数据库表实现脚本

Code

--创建临时表  
CREATE TABLE #tablespaceinfo
    (
      nameinfo VARCHAR(500) ,
      rowsinfo BIGINT ,
      reserved VARCHAR(20) ,
      datainfo VARCHAR(20) ,
      index_size VARCHAR(20) ,
      unused VARCHAR(20)
    )  

--声明表变量  
DECLARE @tablename VARCHAR(255);  

--定义游标  
DECLARE Info_cursor CURSOR
FOR
    SELECT  '[' + [name] + ']'
    FROM    sys.tables
    WHERE   type = 'U';  

--开始游标  
OPEN Info_cursor  
FETCH NEXT FROM Info_cursor INTO @tablename  
 
WHILE @@FETCH_STATUS = 0
    BEGIN 
        INSERT  INTO #tablespaceinfo
                EXEC sp_spaceused @tablename  
        FETCH NEXT FROM Info_cursor  
    INTO @tablename  
    END 

--关闭游标  
CLOSE Info_cursor  
DEALLOCATE Info_cursor  
 
--创建临时表   
CREATE TABLE [#tmptb]
    (
      TableName VARCHAR(50) ,
      DataInfo BIGINT ,
      RowsInfo BIGINT ,
      Spaceperrow AS ( CASE RowsInfo
                         WHEN 0 THEN 0
                         ELSE DataInfo / RowsInfo
                       END ) PERSISTED
    )

--插入数据到临时表  
INSERT  INTO [#tmptb]
        ( [TableName] ,
          [DataInfo] ,
          [RowsInfo]
        )
        SELECT  [nameinfo] ,
                CAST(REPLACE([datainfo], 'KB', '') AS BIGINT) AS 'datainfo' ,
                [rowsinfo]
        FROM    #tablespaceinfo
        ORDER BY CAST(REPLACE(reserved, 'KB', '') AS INT) DESC  


--汇总记录  
SELECT  [tbspinfo].* ,
        [tmptb].[Spaceperrow] AS '每行记录大概占用空间（KB）'
FROM    [#tablespaceinfo] AS tbspinfo ,
        [#tmptb] AS tmptb
WHERE   [tbspinfo].[nameinfo] = [tmptb].[TableName]  and [tbspinfo].[nameinfo] <>'EntNameIndex2' and [tbspinfo].rowsinfo>0
ORDER BY CAST(REPLACE([tbspinfo].[reserved], 'KB', '') AS INT) DESC

去重保留最大值那条

Code

--Year和MCode一样的前提下的重复数据，只要Cu值最大的那条
select * from tbMonitorResults t
where  1=1 and Year between 2016 and 2020
and  not exists(select 1 from tbMonitorResults where Year = t.Year and MCode=t.MCode 
and  Cu > t.Cu)