Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    101

    Unanswered: Track how many objects created on a server

    Hi,
    Does any has a script to track how many objects created on a server on all databases with in specified date range?

    Appreciate your help.
    Thanks

  2. #2
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47
    Code:
    declare
        @datefrom    datetime,
        @dateto        datetime
    
    select     @datefrom    = '2006-01-01',
        @dateto        = '2006-01-24'
    
    create table #sqlcmd
    (
        cmdrow    int    identity(1,1),
        cmdtext    nvarchar(4000)    not null,
        primary key (cmdrow)
    )
    
    create table #result
    (
        database_name    varchar(100),
        no_of_objs    int
    )
    
    insert into #sqlcmd(cmdtext)
    select     'insert into #result select ''' + name + ''', count(*) from ' + name + '..sysobjects where crdate between ''' + 
        convert(varchar(10), @datefrom, 112) + ''' and  ''' + convert(varchar(10), @dateto, 112) + ''''
    from     master..sysdatabases
    
    exec master..xp_execresultset N'select cmdtext from #sqlcmd order by cmdrow', N'master'
    
    select * from #result 
    
    drop table #sqlcmd
    drop table #result
    -----------------
    KH


  3. #3
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Another option,
    Code:
    create table #temp
    (
    databasename varchar(40),
    objects int
    )
    go
    declare
        @datefrom    datetime,
        @dateto        datetime
    declare 
     @sql nvarchar(4000)
    select     
        @datefrom    = '2006-01-01',
        @dateto        = '2006-01-24'
    select @sql='insert into #temp select ''?'',count(*) as count from ?..sysobjects 
    where crdate between ''' + convert(varchar(10), @datefrom, 112) + ''' and  ''' + convert(varchar(10), @dateto, 112) + ''''
    exec sp_MSforeachdb @sql
    go
    select * from #temp
    go
    drop table #temp
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •