Archives

M2M Database Polls

One of my crappy college jobs was in market research. Yes, I was one of those annoying guys in the mall holding a clipboard. I would approach you, politely ask you about your soda preferences (for the enlightened folks we call it Pop), and then take you back into the “store” to do a taste test on several of them. Kind of like the Pepsi Challenge.

It required a few minutes, but it was general painless and we’d send you away with samples of free products. Well, I don’t want to know whether you prefer Coke or Pepsi, but I have what I think are some amazing ideas for reporting on M2M. I need your assistance to determine whether people can/will actually use them. So, in the same vein, please assist me by answering some questions.

How large is your largest M2M database? To determine the database size, simply open Enterprise Manager, navigate to your database, right click on it, and choose Properties. You should see the size of the database on the first page shown (General).

How large is your M2M Database?

View Results

Loading ... Loading ...

Is disk space an issue for you? If I told you I could simply your reporting life by automating most of the reporting process, but it would involve duplicating at least parts of your database, would this cause you an issue? Keep in mind that this process could double or triple your disk usage.

Is disk space an issue for you?

View Results

Loading ... Loading ...

Please paste the following script into Query Analyzer or SQL Server Management Studio. It will report the size of many of the primary tables in your M2M Database. Please respond by copying and pasting the grid into the comments area, like people did with the Orphaned Records Game.

DECLARE @TableName VARCHAR(100)    --For storing values in the cursor
 
--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR 
select [name]
from dbo.sysobjects 
where  OBJECTPROPERTY(id, N'IsUserTable') = 1
and [name] in 
('slcdpm',
         'somast',
         'soitem',
         'sorels',
         'qtmast',
         'qtitem',
         'inmast',
         'inonhd',
         'invend',
         'armast',
         'aritem',
         'apmast',
         'apitem',
         'jomast',
         'joitem',
         'pomast',
         'poitem',
         'lamast',
         'ladetail',
         'shmast',
         'shitem',
         'glmast',
         'glitem',
         'rcmast',
         'rcitem')
FOR READ ONLY
 
--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)
 
--Open the cursor
OPEN tableCursor
 
--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName
 
--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
    --Dump the results of the sp_spaceused query to the temp table
    INSERT  #TempTable
        EXEC sp_spaceused @TableName
 
    --Get the next table name
    FETCH NEXT FROM tableCursor INTO @TableName
END
 
--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor
 
--Select all records so we can use the reults
SELECT tablename as [Table],datasize as [Size]
FROM #TempTable
order by tablename
 
--Final cleanup!
DROP TABLE #TempTable

Thank you for participating, and stay tuned for updates on my new reporting ideas.

Related posts:

18 comments to M2M Database Polls

  • scott

    apitem 148216 KB
    apmast 93752 KB
    aritem 108416 KB
    armast 118064 KB
    glitem 10032 KB
    glmast 168 KB
    inmast 139528 KB
    inonhd 480 KB
    invend 3272 KB
    joitem 24 KB
    jomast 24 KB
    ladetail 0 KB
    lamast 0 KB
    poitem 57024 KB
    pomast 35800 KB
    qtitem 7056 KB
    qtmast 6968 KB
    rcitem 42120 KB
    rcmast 7168 KB
    shitem 56968 KB
    shmast 67256 KB
    slcdpm 5552 KB
    soitem 106560 KB
    somast 72608 KB
    sorels 106952 KB

  • Table Size
    apitem 110136 KB
    apmast 38512 KB
    aritem 53240 KB
    armast 94448 KB
    glitem 4248 KB
    glmast 216 KB
    inmast 130360 KB
    inonhd 39608 KB
    invend 4648 KB
    joitem 29320 KB
    jomast 112624 KB
    ladetail 176696 KB
    lamast 6808 KB
    poitem 65496 KB
    pomast 58496 KB
    qtitem 5200 KB
    qtmast 3984 KB
    rcitem 96896 KB
    rcmast 9552 KB
    shitem 21912 KB
    shmast 72344 KB
    slcdpm 232 KB
    soitem 27904 KB
    somast 67448 KB
    sorels 75208 KB

  • Fred Crawford

    apitem 39616 KB
    apmast 28800 KB
    aritem 41408 KB
    armast 78496 KB
    glitem 16208 KB
    glmast 184 KB
    inmast 17640 KB
    inonhd 464 KB
    invend 1072 KB
    joitem 14664 KB
    jomast 81320 KB
    ladetail 49544 KB
    lamast 2088 KB
    poitem 25864 KB
    pomast 33792 KB
    qtitem 32 KB
    qtmast 32 KB
    rcitem 19560 KB
    rcmast 6280 KB
    shitem 28464 KB
    shmast 81184 KB
    slcdpm 944 KB
    soitem 43344 KB
    somast 32592 KB
    sorels 48960 KB

  • Andrew

    apitem 49648 KB
    apmast 28392 KB
    aritem 30408 KB
    armast 56464 KB
    glitem 7640 KB
    glmast 112 KB
    inmast 16224 KB
    inonhd 5296 KB
    invend 1304 KB
    joitem 21952 KB
    jomast 84600 KB
    ladetail 112304 KB
    lamast 3016 KB
    poitem 33648 KB
    pomast 25576 KB
    qtitem 0 KB
    qtmast 24 KB
    rcitem 32528 KB
    rcmast 6600 KB
    shitem 17928 KB
    shmast 47016 KB
    slcdpm 264 KB
    soitem 26928 KB
    somast 12360 KB
    sorels 29976 KB

  • Catherine

    apitem 24992 KB
    apmast 16400 KB
    aritem 7472 KB
    armast 20488 KB
    glitem 15016 KB
    glmast 480 KB
    inmast 4656 KB
    inonhd 1216 KB
    invend 344 KB
    joitem 4000 KB
    jomast 23144 KB
    ladetail 135232 KB
    lamast 7480 KB
    poitem 14032 KB
    pomast 16056 KB
    qtitem 456 KB
    qtmast 312 KB
    rcitem 23992 KB
    rcmast 3144 KB
    shitem 4656 KB
    shmast 15440 KB
    slcdpm 496 KB
    soitem 7696 KB
    somast 10072 KB
    sorels 9640 KB

  • Judy

    TABLE SIZE
    apitem 182328 KB
    apmast 82104 KB
    aritem 3824 KB
    armast 4208 KB
    glitem 12344 KB
    glmast 288 KB
    inmast 69920 KB
    inonhd 192 KB
    invend 7360 KB
    joitem 2888 KB
    jomast 11368 KB
    ladetail 140120 KB
    lamast 12608 KB
    poitem 143288 KB
    pomast 69264 KB
    qtitem 13728 KB
    qtmast 6672 KB
    rcitem 93648 KB
    rcmast 17320 KB
    shitem 6512 KB
    shmast 10752 KB
    slcdpm 1576 KB
    soitem 6784 KB
    somast 3088 KB
    sorels 5608 KB

  • apitem 128584 KB
    apmast 37064 KB
    aritem 98808 KB
    armast 143440 KB
    glitem 9728 KB
    glmast 200 KB
    inonhd 10616 KB
    invend 3184 KB
    joitem 16576 KB
    jomast 71640 KB
    ladetail 24232 KB
    lamast 824 KB
    poitem 113280 KB
    pomast 52640 KB
    qtitem 20408 KB
    qtmast 22544 KB
    rcitem 105152 KB
    rcmast 17200 KB
    shitem 46272 KB
    shmast 113760 KB
    soitem 116064 KB
    somast 86416 KB
    sorels 75888 KB

  • apitem 38192 KB
    apmast 21016 KB
    aritem 69400 KB
    armast 71904 KB
    glitem 14072 KB
    glmast 232 KB
    inmast 6872 KB
    inonhd 2008 KB
    invend 1672 KB
    joitem 18280 KB
    jomast 80304 KB
    ladetail 0 KB
    lamast 0 KB
    poitem 16376 KB
    pomast 20832 KB
    qtitem 3592 KB
    qtmast 4800 KB
    rcitem 28248 KB
    rcmast 3904 KB
    shitem 30784 KB
    shmast 68288 KB
    slcdpm 20712 KB
    soitem 60408 KB
    somast 72416 KB
    sorels 61400 KB

  • Stephen

    apitem 325320 KB
    apmast 195736 KB
    aritem 377160 KB
    armast 420984 KB
    glitem 12496 KB
    glmast 120 KB
    inmast 438896 KB
    inonhd 4360 KB
    invend 4712 KB
    joitem 36840 KB
    jomast 625232 KB
    ladetail 118976 KB
    lamast 5168 KB
    poitem 150376 KB
    pomast 129312 KB
    qtitem 29384 KB
    qtmast 29528 KB
    rcitem 114848 KB
    rcmast 27304 KB
    shitem 206280 KB
    shmast 325080 KB
    slcdpm 3048 KB
    soitem 383008 KB
    somast 362128 KB
    sorels 379784 KB

  • Rod

    apitem 17216 KB
    apmast 7368 KB
    aritem 3528 KB
    armast 3472 KB
    glitem 1952 KB
    glmast 152 KB
    inmast 5099880 KB
    inonhd 416 KB
    invend 1208 KB
    joitem 2656 KB
    jomast 3969360 KB
    ladetail 0 KB
    lamast 0 KB
    poitem 10408 KB
    pomast 7176 KB
    qtitem 3328 KB
    qtmast 1768 KB
    rcitem 7600 KB
    rcmast 1384 KB
    shitem 2080 KB
    shmast 2544 KB
    slcdpm 848 KB
    soitem 4032 KB
    somast 2320 KB
    sorels 3976 KB

  • Andrew

    Any idea how long it will be before you will elaborate on these “reporting ideas?”

  • Data base @34 gigs
    apitem 147240 KB
    apmast 57296 KB
    aritem 784112 KB
    armast 587424 KB
    glitem 42648 KB
    glmast 424 KB
    inmast 38808 KB
    inonhd 3408 KB
    invend 944 KB
    joitem 139168 KB
    jomast 471824 KB
    ladetail 643824 KB
    lamast 15248 KB
    poitem 108448 KB
    pomast 47648 KB
    qtitem 23360 KB
    qtmast 15032 KB
    rcitem 113168 KB
    rcmast 12304 KB
    shitem 550440 KB
    shmast 484720 KB
    slcdpm 2072 KB
    soitem 1015696 KB
    somast 500824 KB
    sorels 1054784 KB

  • Andrew, as fast as I can. However, I need to test them to ensure that they will work before sharing them.

  • Anthony Agpaoa

    apitem 94240 KB
    apmast 51760 KB
    aritem 193232 KB
    armast 97136 KB
    glitem 14440 KB
    glmast 200 KB
    inmast 253832 KB
    inonhd 488 KB
    invend 3456 KB
    joitem 16040 KB
    jomast 1650776 KB
    ladetail 74464 KB
    lamast 2104 KB
    poitem 51784 KB
    pomast 48368 KB
    qtitem 54624 KB
    qtmast 15648 KB
    rcitem 40584 KB
    rcmast 13152 KB
    shitem 78704 KB
    shmast 77784 KB
    slcdpm 3176 KB
    soitem 187016 KB
    somast 80248 KB
    sorels 145408 KB

  • apitem 138552 KB
    apmast 81872 KB
    aritem 1416608 KB
    armast 606920 KB
    glitem 21344 KB
    glmast 456 KB
    inmast 50576 KB
    inonhd 1680 KB
    invend 5392 KB
    joitem 80976 KB
    jomast 375960 KB
    ladetail 243744 KB
    lamast 5624 KB
    poitem 83584 KB
    pomast 60568 KB
    qtitem 18488 KB
    qtmast 5864 KB
    rcitem 65976 KB
    rcmast 12504 KB
    shitem 776384 KB
    shmast 401408 KB
    slcdpm 1368 KB
    soitem 1986384 KB
    somast 558272 KB
    sorels 2067632 KB

  • Table Size
    apitem 143560 KB
    apmast 50320 KB
    aritem 72016 KB
    armast 125776 KB
    glitem 5560 KB
    glmast 224 KB
    inmast 136552 KB
    inonhd 54088 KB
    invend 5264 KB
    joitem 37504 KB
    jomast 145440 KB
    ladetail 234336 KB
    lamast 8560 KB
    poitem 87360 KB
    pomast 82160 KB
    qtitem 6712 KB
    qtmast 4848 KB
    rcitem 129464 KB
    rcmast 12736 KB
    shitem 28824 KB
    shmast 97328 KB
    slcdpm 248 KB
    soitem 37680 KB
    somast 86928 KB
    sorels 98664 KB

  • SSCOTT896

    apitem 160608 KB
    apmast 53496 KB
    aritem 43608 KB
    armast 70248 KB
    glitem 18992 KB
    glmast 280 KB
    inonhd 7016 KB
    invend 4088 KB
    joitem 34432 KB
    jomast 127584 KB
    ladetail 414504 KB
    lamast 19376 KB
    poitem 131432 KB
    pomast 61864 KB
    qtitem 31856 KB
    qtmast 31248 KB
    rcitem 58480 KB
    rcmast 11200 KB
    shitem 30320 KB
    shmast 49296 KB
    soitem 58824 KB
    somast 52656 KB
    sorels 42168 KB

  • Jason

    apitem 604128 KB
    apmast 199904 KB
    aritem 102528 KB
    armast 76864 KB
    glitem 48968 KB
    glmast 320 KB
    inmastx 197552 KB
    inonhd 16528 KB
    invend 15416 KB
    joitem 223056 KB
    jomast 762432 KB
    ladetail 538104 KB
    lamast 28176 KB
    poitem 455376 KB
    pomast 232992 KB
    qtitem 9592 KB
    qtmast 10456 KB
    rcitem 335920 KB
    rcmast 48712 KB
    shitem 52992 KB
    shmast 58320 KB
    soitem 83088 KB
    somast 64320 KB
    sorels 61152 KB

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>