收集sql server数据库内所有表信息，表名称，表创建日期，等
--生成数据库所有表及数据行 begin use database_name drop table ZP_MachinesRows create table ZP_MachinesRows (name varchar(128) not null,crdate datetime not null,rows int, max_datetime datetime ,min_datetime datetime ,date_state varchar(4)) --插入名称创建时间及行数 insert into ZP_MachinesRows(name,crdate,rows) select a.name,a.crdate,b.rows from sys.sysobjects as a inner join sys.sysindexes as b on a.id=b.id where a.type='u' and b.indid<2 and a.name like 'SYS%' order by a.name --,b.rows desc --更新表内最新时间，计算表使用状态 declare @t_name varchar(20) declare @sql varchar(1000) declare @count int declare @i int declare @maxdate datetime declare @mindate datetime select ROW_NUMBER() over(order by name)as rw ,name into #temp_name from ZP_MachinesRows select @i=min(rw) from #temp_name while @i is not null begin select @t_name=name from #temp_name where rw=@i set @sql='update ZP_MachinesRows set max_datetime=a,min_datetime=b'+' from (select max(createtime) as a,min(createtime) as b from '+@t_name +') as c '+'where ZP_MachinesRows.name='''+@t_name+'''' exec (@sql) select @i=min(rw) from #temp_name where rw>@i end drop table #temp_name update ZP_MachinesRows set date_state=(case when datediff(d,max_datetime,getdate())<10 then 'use' else 'stop' end) end