SQL查询根据列名获取表名和行数”

Question

我已经声明了一个 Cursor 来获取表名，并且没有基于列名的表中的列。请找到下面的查询表名没有被插入。请建议。

Create table #t
(
tabname varchar(500),
NoOfRows bigint,
)

Declare @Namee Varchar(500)
Declare @GetName Cursor
Set     @Getname = Cursor for 
Select table_name from information_Schema.columns
where column_name='isactive'Open @Getname
Fetch Next From @Getname into @Namee
While @@Fetch_Status=0
Begin 
--Print @Namee
insert into #t(tabname) SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE  TABLE_NAME =' + @Namee + '
exec ('insert into #t(NoOfRows)  Select count(*) from ' + @Namee + ' where isactive=0')
Fetch Next From @Getname into @Namee
End
Close @GetName
Deallocate @GetName
select * from #t 

Answer 1

您可以在单个 INSERT 中插入表名和行数:

EXEC('INSERT INTO #t
         (tabname, NoOfRows)  
      SELECT '''+ @Namee +''', COUNT(*) 
        FROM ' + @Namee + ' 
       WHERE isactive = 0')

您所拥有的表名和计数之间没有任何联系，因此您不太可能缺少表，但 NoOfRows 值得怀疑。实际上与记录中的表名相关联。