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).
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.
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.
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
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
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
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
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
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
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
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.
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
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
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