Extracting DMV information from SQL Server

Extracting DMV information from SQL Server:

1. Build view to get information

create view justdave_dmvs as
select
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,
c.precision,
c.scale
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

Advertisement

---