Post reply

Capture database name or database Id in dbcc show_statisics result

  • January 12, 2015 at 10:36 am

    #300186

    I am trying to collect the result of dbcc show_statistics('table1', pk_table1) in a working table from about 100 databases that has the same table1 schema . Along with the result I want to capture the database Id or database name in the work table .

    I need to capture database id or database name as I will be running the query through multiple databases after putting them in a cursor

    here is my script

    use db_monitor

    GO

    create table SQL_Monitor_Statistics values([Name]

    , [Updated]

    , [Rows]

    , [Rows Sampled]

    , [Steps]

    , [Density]

    , [Average key length]

    , [String index] )

    insert into SQL_Monitor_Statistics exec ('dbcc show_statistics ( ''table1'', ''pk_table1'') with STAT_HEADER')

  • January 12, 2015 at 11:22 am

    #1769590

    modify your table to have the database name in it.

    explicitly name your columns in the insert statement.

    since this is in a cursor/loop, just udate the database name where it is null as a seconfd step:

    INSERT INTO SQL_Monitor_Statistics([Updated] , [Rows] , [Rows Sampled], [Steps], [Density] , [Average key length] , [String index] )

    dbcc show_statistics ( 'table1', 'pk_table1') with STAT_HEADER

    UPDATE SQL_Monitor_Statistics SET database_name = DB_NAME() WHERE database_name IS NULL

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 12, 2015 at 12:33 pm

    #1769615

    Thank you!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply