存储过程:
- DECLARE @DatabaseName varchar(255)
- DECLARE @TableName varchar(255)
- set nocount on
- Declare Database_Cursor Cursor for select name from master.dbo.sysdatabases where dbid>4
- OPEN Database_Cursor
- FETCH NEXT FROM Database_Cursor INTO @DatabaseName
- WHILE(@@FETCH_STATUS=0)
- BEGIN
- PRINT convert(varchar(12),getdate(),101) + ' ' + ' 数据库名：' + @DatabaseName
- exec( 'SELECT ''' + '||' + ''' as id,''' +@DatabaseName+ ''' as databasename,name,crdate,refdate into #ddd FROM ' +@DatabaseName+ '..sysobjects WHERE xtype=''U'' and datediff(day,crdate,getdate())=0; select * from #ddd;drop table #ddd')
- FETCH NEXT FROM Database_Cursor INTO @DatabaseName
- END
- CLOSE Database_Cursor
- DEALLOCATE Database_Cursor
- GO
配合批处理就可以获得多台sql server 服务器的新表:
- set aTime=%time%
- set bDate=%date:~4,20%
- set bTime=%time:~0,2%-%time:~3,2%
- osql -S192.168.0.192 -U sa -P sa -n -w 200 -i "D:\log_collect\sqlmonitor\sqlNewTableQuery.sql" -o "D:\log_collect\sqlmonitor\monitorbyday\servername%bDate%.txt"
- quit
- exit
