List of Databases

Back in April I posted a query that shows instance information.  This post is the next level down in the hierarchy that I collect on my database platforms each week…  The databases in an instance.

This query is certainly one of the more straight forward queries to capture database data.  It probably doesn’t really need to be executed / collected weekly, but I do it anyway to be consistent with my collection process.

If you plug this query into a SQL Server Central Management server you’ll have a snapshot of all your instances that are part of the CMS.

This query will return the database name, the overall size of the database including all data and log files, the create date, owner, compatibility level, online vs offline, and update stats info.

It’s interesting to find databases owned by staff that are no longer around, compatibility levels to old versions for no obvious reason, and create and update stats that are turned off.

Like they say on the History Channel’s Pawn Star show… you never know WHAT will be coming through that door.

So here’s the query:

SELECT    CONVERT(int, CONVERT(char, current_timestamp, 112)) AS CaptureDate
        , d.database_id
        , d.[name]
        , CAST(SUM(mf.size * 8096.0 / 1000000000) AS NUMERIC(18, 2)) AS [db_size (G)]
        , d.recovery_model_desc AS [Recovery Model]
        , d.create_date
        , suser_sname(d.owner_sid) AS [Owner]
        , d.[compatibility_level]
        , d.state_desc AS [State]
        , d.is_auto_create_stats_on
        , d.is_auto_update_stats_on
FROM        sys.databases d
        INNER JOIN sys.master_files mf
            ON    mf.database_id = d.database_id
WHERE    d.database_id > 4    — Exclude the system databases.
GROUP BY d.database_id
        , d.[name]
        , d.recovery_model_desc
        , d.create_date
        , d.owner_sid
        , d.[compatibility_level]
        , d.state_desc
        , d.is_auto_create_stats_on
        , d.is_auto_update_stats_on

My next post in this series will be on database backups, which is a much more interesting query AND really does need to be monitored weekly, minimum.

Stay curious… keep learning…


Leave a Reply

You must be logged in to post a comment.

Using Gravatars in the comments - get your own and be recognized!

XHTML: These are some of the tags you can use: <a href=""> <b> <blockquote> <code> <em> <i> <strike> <strong>

why is it important to forgive essay esl definition essay ghostwriter sites help writing professional cheap essay on shakespeare popular curriculum vitae writers for hire professional cv writing services ca professional case study writer site uk order best admission essay on civil war esl university essay ghostwriter websites for school top letter writers service usa popular university essay ghostwriting services esl thesis proposal editor website uk best school research paper examples papers writer services online top reflective essay writers site for college best content ghostwriters website for mba custom essay ghostwriting for hire ca custom cv ghostwriters service usa pay for my popular academic essay custom essays writer service for masters cheap article proofreading services usa best presentation editor for hire usa zora neale hurston and langston hughes help with personal essay on donald trump cheap expository essay writers website gb dissertation proposal ghostwriter services us help me write top definition essay on shakespeare help with my communication dissertation introduction professional mba bibliography sample popular dissertation conclusion writers site for university professional custom essay ghostwriters service gb write popular movie review top descriptive essay editing services for college esl problem solving writing sites for college professional critical analysis essay proofreading site for mba popular research proposal writers for hire for masters professional cheap essay writing site for phd buy law case study esl phd dissertation introduction topics humans are inherently evil esl homework writer websites for phd professional cheap essay writer for hire usa esl article writing service for mba best critical thinking proofreading websites uk best thesis statement proofreading site usa write me professional research proposal professional proofreading site usa cheap blog ghostwriter for hire for college type my research proposal online write sociology bibliography top mba application letter advice write my professional personal essay on trump cheap article ghostwriters for hire for college greek doctrine of ethos pay for popular curriculum vitae best article ghostwriter website for phd esl dissertation hypothesis editor websites gb professional business plan ghostwriters website uk custom college personal essay ideas top admission essay editing websites us write custom masters essay on lincoln pay to do popular expository essay cheap essays writing service for college cheap cv ghostwriting website for phd popular problem solving editor for hire for college top reflective essay writing websites for school best curriculum vitae ghostwriter site for college custom essays ghostwriters websites au top school essay ghostwriters sites usa help writing culture term paper best article editor websites usa write my scholarship essay online cheap thesis statement writers websites uk top essays ghostwriting website for mba esl thesis editing websites ca annotated bibliography ghostwriting site online professional dissertation abstract writing site online best dissertation hypothesis writing websites ambiguity in young goodman brown the morality of euthanasia custom movie review writers for hire for mba best dissertation abstract writers websites au help me write music course work write me technology annotated bibliography professional biography editing sites for college esl personal statement proofreading services for college pay for my political science letter custom curriculum vitae editor services uk popular ghostwriters website us analysis essay proofreading services gb write my geology thesis proposal write ecology dissertation introduction words to describe a mother buy classic english literature dissertation conclusion write me top cheap essay on usa cheap report editor websites us professional cv ghostwriter services popular best essay editor site uk top cheap essay writing for hire au top presentation ghostwriting website for masters professional biography ghostwriting websites online resume proofreading website au esl dissertation abstract ghostwriter for hire uk esl phd application letter samples esl critical analysis essay ghostwriter website for masters popular dissertation methodology ghostwriters services gb buy medicine dissertation introduction custom presentation ghostwriter for hire for college definition proofreading for hire online type my professional expository essay on pokemon go best report writers for hire for mba best critical thinking ghostwriter for hire au cheap annotated bibliography writer for hire for masters popular descriptive essay proofreading for hire ca best thesis proposal writer websites online help with my top expository essay on founding fathers top book review writing service au custom university cover letter example custom rhetorical analysis essay writing site au pay for my sociology creative writing cheap persuasive essay writing service gb top dissertation introduction writing website online pay to write finance thesis statement top course work ghostwriters website usa best university essay writer services usa best reflective essay writers sites ca cheap reflective essay proofreading for hire usa best critical analysis essay writing sites for masters type my custom argumentative essay on civil war personal statement editor website au help writing management movie review write my custom definition essay on founding fathers custom dissertation introduction ghostwriter site ca professional home work writer sites online article ghostwriting site rip van winkle text thesis ghostwriters website usa best bibliography editor for hire gb top dissertation abstract editing service for college pay for my law term paper custom dissertation proofreading sites gb dissertation methodology writer website us best literature review ghostwriters for hire esl academic essay ghostwriting site au professional persuasive essay ghostwriting services us best bibliography writers site usa top creative writing proofreading website online esl college essay editor service for university help with esl creative essay on lincoln professional cv writer site gb professional research paper writing website for college esl university essay ghostwriter websites for university do my professional expository essay on trump cheap persuasive essay writers website au popular research proposal editor website for masters esl dissertation abstract editor services for phd best curriculum vitae writers services ca custom descriptive essay ghostwriting site uk esl blog writer websites for masters pay to do engineering thesis proposal professional critical essay ghostwriting website gb examples for papers esl content proofreading sites usa top analysis essay writers for hire us expository ghostwriter website au esl persuasive essay ghostwriter site online dissertation results ghostwriting website usa best descriptive essay proofreading site ca popular cv writing site for university top thesis proposal writing websites au professional paper proofreading for hire for university top college reflective essay assistance professional critical essay editing services for school custom term paper proofreading services for university pay to do mathematics curriculum vitae cheap analysis essay writers services for college best presentation ghostwriters sites for mba professional phd essay ghostwriter sites for college help writing top presentation custom curriculum vitae ghostwriters services for college professional definition essay editing sites ca esl presentation writers services ca write me algebra dissertation introduction custom school masters essay assistance esl masters academic essay example write me cheap expository essay on brexit best critical essay ghostwriter websites for phd popular cv editor websites for masters professional home work ghostwriting service for university write me cheap course work online help writing u.s. history and government dissertation abstract pay to do medicine admission essay best papers writers website ca essay writing site uk popular speech writing services uk professional best essay editor services au order professional personal essay professional persuasive essay ghostwriter site for mba esl reflective essay editing websites ca speech writer websites uk cheap course work writing websites for masters men at forty poem help with my custom expository essay on lincoln professional report writer for hire for university pay to get women and gender studies paper example of fate do my world affairs resume custom personal statement proofreading sites for phd esl biography ghostwriter services gb management essay editing website top critical thinking editor website online admission essay ghostwriting websites gb popular school essays help popular business plan proofreading services for mba persuasive editing websites usa best school reflective essay ideas professional critical analysis essay proofreading sites us cheap bibliography editing site ca best article writer website for masters custom case study ghostwriter websites for mba math editor websites advice for speech cheap blog post writer for hire us top papers editor service ca esl thesis proposal editor service for college cheap custom essay ghostwriters for hire ca custom application letter ghostwriter websites gb speech ghostwriter websites popular curriculum vitae ghostwriter for hire uk write me statistics home work professional essay ghostwriter services for school do my u.s. history and government dissertation methodology top home work proofreading sites for college correlation experiment ideas phd essay writing sites us popular cheap essay editing sites for masters buy political science dissertation hypothesis top research paper writer sites for university do my best reflective essay buy u.s. history and government course work custom creative writing editing service best curriculum vitae ghostwriters websites gb order women and gender studies admission paper cheap creative writing writing service gb type my custom college essay on founding fathers cheap speech writer websites ca help me write custom admission essay on hillary clinton best cheap essay writers for hire gb help with best critical analysis essay on donald trump custom personal essay ghostwriters website for school top masters course work samples pay to do medicine research proposal professional speech ghostwriters sites ca popular mba critical analysis essay topics esl writing for hire for mba professional dissertation methodology editing website au cheap blog ghostwriter service usa professional content proofreading sites for masters top dissertation introduction ghostwriters for hire for mba best research paper writers service us buy popular definition essay on presidential elections esl resume editor site gb best bibliography writing service for masters top literature review writers site uk pay to do best critical essay online pay to write logic cover letter thesis statement ghostwriting sites gb custom papers writing services usa rhetorical analysis editor service us cheap scholarship essay editor sites for mba best dissertation conclusion editor websites for mba custom application letter editing site popular custom essay ghostwriters for hire au best definition essay editing website gb the book flush pay for astronomy course work pay to write custom application letter online zoology writing for hire popular essays writer services for phd order professional creative essay critical analysis writer for hire au custom case study ghostwriter websites for school help with my custom application letter top movie review ghostwriting websites for university reflective essay writing websites online custom presentation writer for hire for mba essay proofreading websites ca type my economics dissertation introduction popular content ghostwriters website for mba top cover letter ghostwriting websites for college custom movie review proofreading website for university esl admission paper writing website ca custom dissertation chapter writing sites usa best phd essay writing sites for masters esl dissertation proposal writing site usa best letter ghostwriters service online esl school essay ghostwriter websites for university help with my top expository essay on civil war cheap term paper ghostwriter website for college cheap critical analysis essay writers for hire us best curriculum vitae proofreading websites us do my popular academic essay on usa pay for my investments term paper shakespeare in the bush summary cheap mba movie review assistance top dissertation introduction writers websites uk cheap rhetorical analysis essay ghostwriters site usa cheap academic essay editor site gb popular descriptive essay proofreading sites esl essay writers service usa esl dissertation methodology writing website usa popular college essay ghostwriter website for college top biography ghostwriter websites for mba popular literature review proofreading site us professional presentation proofreading sites online professional essay writers services custom dissertation hypothesis ghostwriting for hire usa custom phd critical analysis essay advice cheap creative writing editor site for university professional creative essay ghostwriting service for masters popular dissertation introduction writers sites online esl masters essay editing sites for masters professional dissertation results writer websites au rhetorical analysis writing website online cheap dissertation chapter writer websites esl article writers services for mba esl resume writer site for masters help writing geography application letter best dissertation abstract editing websites for college esl case study proofreading sites online esl curriculum vitae editing sites for phd annotated bibliography proofreading service uk popular scholarship essay ghostwriting websites argumentative ghostwriting website uk children need to play not compete jessica statsky top home work ghostwriter websites au custom bibliography writing websites au esl expository essay editor website for university zoology essay ghostwriters websites thomas jefferson contributions esl phd article example cheap creative essay editor site for university custom thesis editing websites for college finance essay ghostwriting websites essays on food cheap research proposal editor site buy mathematics article ups human resources popular custom essay ghostwriters site ca analysis writer services esl article review ghostwriters service au pay to write economics assignment pay to do ecology letter top dissertation conclusion ghostwriting websites best article writer websites us custom college problem solving samples professional definition essay editor sites for phd cheap critical essay editing websites for university esl annotated bibliography editor for hire for mba the rams football team pay to write custom dissertation abstract popular blog post ghostwriting site for college best dissertation hypothesis ghostwriters websites us esl resume ghostwriting websites best cover letter writer website for university best phd essay writer sites online cheap letter editor site for college custom homework writers for hire for masters definition essay on criminal law esl speech ghostwriting websites for mba professional assignment ghostwriting website uk professional article review proofreading for hire for masters custom phd masters essay samples esl critical analysis essay ghostwriters service online pay to get best rhetorical analysis essay on lincoln help with my ancient civilizations report custom business plan ghostwriting sites for mba write speech paper custom personal statement proofreading site usa cheap business plan editing websites for university help writing english as second language course work esl research paper writers websites online esl dissertation hypothesis proofreading website for school esl dissertation writers services social commentary examples top curriculum vitae writer sites au write my english as second language annotated bibliography top dissertation introduction writers websites for school cheap reflective essay ghostwriters for hire us write best argumentative essay on pokemon go type my family and consumer science letter top article review ghostwriters sites for phd top book review writer site uk professional report writers services for phd professional critical thinking editing services professional article review writer sites for university write my professional custom essay on hillary best case study writing sites for mba esl school essay writers websites ca do my drama case study popular scholarship essay editor site for mba write me top best essay on founding fathers help with cheap essay on hacking cheap admission essay ghostwriter services online write my cheap content definition essay writer site gb custom curriculum vitae writers site cheap critical analysis essay writer sites for mba cheap dissertation introduction proofreading service uk pay to do physics dissertation introduction popular speech writing site report ghostwriting site usa term paper editor website us help writing earth science personal statement help me write tourism article popular ghostwriters service for university rhetorical analysis letter from birmingham jail essay best research paper editor site uk popular university essay writer site for mba cheap literature review editor site online top creative writing ghostwriters services us best cv editor websites for mba custom school bibliography samples cheap phd cover letter help best case study proofreading website for school pay for custom descriptive essay online professional research paper writing website us top university essay editor websites for university mark fossie men at forty poem cheap assignment editor site esl speech writers website ca dramatic irony in lord of the flies custom speech proofreading website us professional thesis proofreading websites for phd first confession frank o connor summary popular analysis essay editing service uk write my environmental studies letter cheap admission paper editor for hire for university esl blog post editor website for college custom application letter ghostwriter websites for school custom dissertation methodology ghostwriters services for college popular best essay writing website ca book review proofreading for hire usa best academic essay editor service ca help with my cheap college essay on usa the crucible elizabeth cheap custom essay editing sites usa best thesis statement ghostwriter website for school pay for best masters essay on hacking popular course work ghostwriter site for phd custom definition essay ghostwriters sites online esl reflective essay writer services uk esl masters admission essay help professional reflective essay ghostwriters websites us overreaction to cholesterol top phd article ideas write composition thesis best university application letter topics cheap annotated bibliography writing services for college professional resume writing for hire for mba top movie review proofreading for hire for school professional dissertation proposal ghostwriters for hire gb custom blog ghostwriters sites for phd esl application letter ghostwriters websites for college top creative essay writers services au order professional custom essay on hacking popular problem solving editor websites for university professional personal essay writers websites gb professional critical thinking writer service for mba best article review ghostwriters site for school write cheap best essay on brexit top home work writer site uk best dissertation conclusion ghostwriters services online pay for professional dissertation methodology online custom custom essay ghostwriters services for masters professional critical essay ghostwriters service for college best presentation ghostwriter service for college professional academic essay on hillary esl creative writing ghostwriter service gb top speech editor sites for mba physics essay ghostwriter site pay for my engineering article esl masters essay proofreading website for school rhetorical analysis essay writer sites best definition essay ghostwriting sites uk top movie review writer services gb personal essay on earth science expository essay ghostwriters websites ca write me environmental studies home work professional homework proofreading websites for college best dissertation introduction ghostwriter sites usa write my engineering admission paper best letter editing services us popular admission essay proofreading site usa best phd essay editing services gb popular persuasive essay proofreading site au cheap critical analysis essay proofreading sites usa top annotated bibliography ghostwriting services us cheap thesis statement writer websites au esl college essay ghostwriters site for school