Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2006
    Posts
    9

    Unanswered: can anyone help me in figuringout why this query is not working in 11.9

    select '*********This script collects database and database objects information*********'
    go
    use tempdb
    go

    if exists (select 1 from tempdb..sysobjects where name = 'dbusers' and type = 'U')
    begin
    drop table tempdb..dbusers
    end
    if exists (select 1 from tempdb..sysobjects where name = 'dbobjects' and type = 'U')
    begin
    drop table tempdb..dbobjects
    end
    if exists (select 1 from tempdb..sysobjects where name = 'tablesizes' and type = 'U')
    begin
    drop table tempdb..tablesizes
    end
    if exists (select 1 from tempdb..sysobjects where name = 'collectdbinfo' and type = 'P')
    begin
    drop procedure collectdbinfo
    end
    go

    create table tempdb..dbusers (
    dbname varchar(30),
    usercount int
    )
    go
    create table tempdb..dbobjects (
    dbname varchar(30),
    objecttype char(2),
    objectcount int
    )
    go
    create table tempdb..tablesizes (
    dbname varchar(30),
    tablename varchar(30),
    tablesize_in_MB int
    )
    go

    create procedure collectdbinfo as
    declare @dbname varchar(30)
    declare @cmd varchar(255)

    declare DBNAME_CUR CURSOR for select name from master..sysdatabases for read only

    open DBNAME_CUR


    FETCH DBNAME_CUR into @dbname
    while @@SQLSTATUS = 0
    BEGIN

    select @cmd =
    'insert into tempdb..dbusers select ''+
    @dbname+'', count(*) from '+@dbname+'..sysusers'
    exec (@cmd)

    select @cmd =
    'insert into tempdb..dbobjects select ''+
    @dbname+'', type, count(*) from '+ @dbname+'..sysobjects group by type'
    exec ( @cmd)

    select @cmd = 'insert into tempdb..tablesizes select ''+
    @dbname+'', a.name, b.pagecnt/512 from '+@dbname+'..sysobjects a, ' +
    @dbname+'..systabstats b where b.indid in (0, 1) and a.id = b.id and a.type = 'U''

    exec (@cmd)
    select @cmd

    FETCH DBNAME_CUR into @dbname
    END
    CLOSE DBNAME_CUR
    DEALLOCATE cursor DBNAME_CUR
    go



    exec tempdb..collectdbinfo
    go

    select * from tempdb..dbusers order by dbname
    go
    select * from tempdb..dbobjects order by dbname, objecttype
    go
    select * from tempdb..tablesizes order by dbname, tablename
    go
    drop table tempdb..dbusers
    go
    drop table tempdb..dbobjects
    go
    drop table tempdb..tablesizes
    go
    drop procedure collectdbinfo
    go


    use tempdb
    go
    print '*********************Object details******************'
    go

    if exists (select 1 from tempdb..sysobjects where name = 'obj_details' and type = 'P')
    begin
    drop procedure obj_details
    end
    go

    use tempdb
    go
    create proc obj_details
    as
    declare @dbname varchar(30)
    declare @cmd varchar(250)
    declare @cmd1 varchar(250)
    declare @msg varchar(250)
    declare @obj_name varchar(250)
    declare @obj_type char(10)
    declare @obj_owner varchar(30)
    declare DBNAME_CUR CURSOR for select name from master..sysdatabases where name not in ('sybsystemprocs','tempdb') for read only
    open DBNAME_CUR

    create table #obj_table (dbname varchar(50),obj_name varchar(50), type char(2),obj_owner varchar(30))

    FETCH DBNAME_CUR into @dbname
    while @@SQLSTATUS = 0
    BEGIN
    select @cmd="insert into #obj_table select '"+@dbname+"', A.name,A.type,B.name from "+@dbname+"..sysobjects A,"+@dbname+"..sysusers B where A.uid= B.uid and type!='S'"
    exec (@cmd)
    FETCH DBNAME_CUR into @dbname
    END
    CLOSE DBNAME_CUR
    DEALLOCATE cursor DBNAME_CUR

    select 'Total Objects',count(*) from #obj_table

    declare OBJ_CUR CURSOR for select dbname,obj_name,type, obj_owner from #obj_table order by dbname,type,obj_name for read only
    open OBJ_CUR

    FETCH OBJ_CUR into @dbname,@obj_name,@obj_type,@obj_owner
    while @@SQLSTATUS = 0
    BEGIN
    print "************************************************* ********************"
    select @msg="DBName: "+@dbname+"
    Object Name: "+@obj_name+"
    Object Type: "+@obj_type+"
    Object Owner:"+@obj_owner

    print @msg
    if @obj_type='U'
    BEGIN
    select @cmd="select 'rowcount', count(*) from "+@dbname+"."+@obj_owner+"."+@obj_name
    print @cmd
    exec (@cmd)
    select @cmd=@dbname+"..sp_help"
    select @cmd1=@obj_owner+"."+@obj_name
    print @cmd1
    exec @cmd @cmd1
    END

    if @obj_type='P' or @obj_type='TR' or @obj_type='V'
    BEGIN
    select @cmd=@dbname+"..sp_help"
    select @cmd1=@obj_name
    exec @cmd @cmd1
    select @cmd=@dbname+"..sp_helptext"
    select @cmd1=@obj_name
    exec @cmd @cmd1
    END



    FETCH OBJ_CUR into @dbname,@obj_name,@obj_type,@obj_owner
    END
    CLOSE OBJ_CUR
    DEALLOCATE cursor OBJ_CUR

    go

    exec tempdb..obj_details
    go
    drop procedure obj_details
    go

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    It's very simple - the EXEC (@CMD) syntax is not supported in 11.x.

    You may be able to get around this with the sp_remotesql stored procedure.

    Michael

  3. #3
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255

    Alternative

    Do it in two passes. In the first pass (your code as is) PRINT @cmd but do not execute it. Redirect the results (all the printed output) to a file, say mysql.txt. In the second pass:
    isql -imysql.txt -omyoutput.txt ...
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

Posting Permissions

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