DECLARE CountTableRecords CURSOR READ_ONLY FOR
--我需要在外层加一个数遍历数据库名称的循环.
--根据数据库名称遍历表名的循环.
--在根据表名进行查询数据信息
--这句是获得当前库中所用的用户表面
SELECT sst.name,
Schema_name(sst.schema_id)
FROM sys.tables sst
WHERE sst.TYPE = 'U'
DECLARE @name VARCHAR(80),
@schema VARCHAR(40)
OPEN CountTableRecords
-- 根据表名进行遍历统计表数据信息.
FETCH NEXT FROM CountTableRecords INTO @name, @schema
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
IF ( @@FETCH_STATUS <> -2 )
BEGIN
PRINT @name
DECLARE @sql NVARCHAR(1024)
SET @sql='DECLARE @count INT SELECT @count=COUNT(1) FROM ' + Quotename(@schema)
+
'.' + Quotename(@name) +' PRINT @count'
EXEC Sp_executesql @sql
END
FETCH NEXT FROM CountTableRecords INTO @name, @schema
END
CLOSE CountTableRecords
DEALLOCATE CountTableRecords
GO
---------------创建表记录表记录
create table tableRecord
(
tableName varchar(50) not null,
countRecord int null,
identRecord int null
)
go
--这是动态读取数据库名称,根据数据库名称获取当前用户表名
create procedure getTableRecord
as
--清空数据
truncate table tableRecord
--定义临时表
declare @tables table(name varchar(50))
declare @databaseName varchar(200)
declare @tbName varchar(200)
declare cursor_database cursor for
select name from [sysdatabases] where crdate>=convert(varchar,getdate(),23) order by [name]
open cursor_database
fetch next from cursor_database into @databaseName
while @@fetch_status=0
begin
insert into @tables
exec('select name from '+@databaseName+'.sys.tables')
declare cursor_tb cursor for
select name from @tables order by [name]
open cursor_tb
fetch next from cursor_tb into @tbName
while @@fetch_status=0
begin
insert into tableRecord
exec('select '''+@tbName+''',count(*),ident_current('''+@databaseName+'.dbo.'+@tbName+''') from ['+@databaseName+'].dbo.['+@tbName+']')
fetch next from cursor_tb into @tbName
end
close cursor_tb
deallocate cursor_tb
fetch next from cursor_database into @databaseName
end
close cursor_database
deallocate cursor_database
USE [TEST]
GO
/****** Object: StoredProcedure [dbo].[getTableRecord] Script Date: 03/26/2013 10:57:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--这是动态读取数据库名称,根据数据库名称获取当前用户表名
ALTER procedure [dbo].[getTableRecord]
as
--清空数据
truncate table FirewallLog
--定义临时表存储表名
declare @tables table(name varchar(50))
--数据库名称
declare @databaseName varchar(200)
--数据库所对应的表名
declare @tbName varchar(200)
declare cursor_database cursor for
select
data.name
from sys.databases data
where Convert(varchar(100),create_date,23)=CONVERT(varchar(90), GETDATE(), 23)
and SUBSTRING(data.name,17,3) ='FWS'
order by [name]
open cursor_database
fetch next from cursor_database into @databaseName
while @@fetch_status=0
begin
insert into @tables
exec('select ''dbo.''+name from '+@databaseName+'.sys.tables')
declare cursor_tb cursor for
select name from @tables order by [name]
open cursor_tb
fetch next from cursor_tb into @tbName
while @@fetch_status=0
begin
insert into FirewallLog
exec('select * from ['+@databaseName+'].'+@tbName+'')
fetch next from cursor_tb into @tbName
end
close cursor_tb
deallocate cursor_tb
fetch next from cursor_database into @databaseName
end
close cursor_database
deallocate cursor_database
