Extracting DMV information from SQL Server

Extracting DMV information from SQL Server:

  1. Build view to get information

create view justdave_dmvs as
SERVERPROPERTY(‘productversion’) productversion,
SERVERPROPERTY (‘productlevel’) productlevel,
SERVERPROPERTY (‘edition’) edition,
REPLACE(REPLACE(REPLACE(@@VERSION, CHAR(13),”),CHAR(10),”),CHAR(9),”)serverversion,
b.name tabname,
c.name colname,
d.name typename,
from sys.schemas a,
sys.all_objects b,
sys.all_columns c,
sys.types d
where a.name=’sys’
and a.schema_id=b.schema_id
and OBJECTPROPERTY(b.object_id, ‘IsMSShipped’) = 1
and b.name like ‘%dm_%’
and b.type in (‘V’, ‘TF’, ‘IF’)
and b.object_id=c.object_id
and c.system_type_id=d.system_type_id

2. BCP out to csv file

bcp “select * from justdave_dmvs” queryout 2008R2X1.csv -t, -c -S WIN-TOU50O0VHP8\SQL2008R2X1 -T


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s