Confidential and proprietary materials for authorized Verizon personnel and outside agencies only. Use, disclosure or distribution of this material is not permitted to any unauthorized persons or third parties except by written agreement.

PID#

MS SQL Server data dictionary

Joe Forrar – Verizon Corporation

Newark, NJ Nov 9, 2013

Confidential and proprietary materials for authorized Verizon personnel and outside agencies only. Use, disclosure or distribution of this material is not permitted to any unauthorized persons or third parties except by written agreement. 2

Background

Joe Forrar IA, Compliance Framework Lots of irons in the fire: cross BU, cross channel, cross areas of interest Different data sources

Challenges Over 2 dozen players High value in cross pollination Transitional by design High tech and high touch

Energy drains Multiple people – redoing same task Same data – different “as of” dates Pioneers renaming columns, tables Meetings, emails, conference calls

Same columns – different formats

Confidential and proprietary materials for authorized Verizon personnel and outside agencies only. Use, disclosure or distribution of this material is not permitted to any unauthorized persons or third parties except by written agreement. 3

Overview

1. Scope and flow MS SQL Server Run on each server Size, attributes Collate results on each server Consolidate results on reporting server Snap Tableau on top

2. Tableau Dashboard Data dictionary view Bar chart view Database size view Overall server size view

3. Summary Generation script Linked servers

Confidential and proprietary materials for authorized Verizon personnel and outside agencies only. Use, disclosure or distribution of this material is not permitted to any unauthorized persons or third parties except by written agreement. 4

Scope and Flow

Implemented on Microsoft SQL Server Querying system tables, two collectors run: DB sizing Table, column attributes Each thread creates its own table One feeder job combines the results at each server level Combined results are then appended

Confidential and proprietary materials for authorized Verizon personnel and outside agencies only. Use, disclosure or distribution of this material is not permitted to any unauthorized persons or third parties except by written agreement. 5

Job 1 Calling sproc

• This stored procedure generates database master list…. Cursor of dbs

Collectors Detail tables

Confidential and proprietary materials for authorized Verizon personnel and outside agencies only. Use, disclosure or distribution of this material is not permitted to any unauthorized persons or third parties except by written agreement. 6

Job 1 Calling sproc continued

• We clear the detail tables and call detail collectors for size and columns..

Counting dbs Details tables init Walk db cursor

Confidential and proprietary materials for authorized Verizon personnel and outside agencies only. Use, disclosure or distribution of this material is not permitted to any unauthorized persons or third parties except by written agreement. 7

Detail table

• Detail table responsible for sizing info..

Identifying data down to physical file part Track configured space Track used space

Confidential and proprietary materials for authorized Verizon personnel and outside agencies only. Use, disclosure or distribution of this material is not permitted to any unauthorized persons or third parties except by written agreement. 8

Detail table

• Detail table responsible for columnar attributes..

Identifying data from server to column Data typing, relational roles, description

Confidential and proprietary materials for authorized Verizon personnel and outside agencies only. Use, disclosure or distribution of this material is not permitted to any unauthorized persons or third parties except by written agreement. 9

Size collector

• Works down to database sizing

Dynamically construct the stored procedure Database name is fed in from calling sproc, looping through db_Cursor

Confidential and proprietary materials for authorized Verizon personnel and outside agencies only. Use, disclosure or distribution of this material is not permitted to any unauthorized persons or third parties except by written agreement. 10

Attribute collector

• Works down to column level

Dynamically constructed stored procedure Scans through the server for each database Clear out any previous entries – allows reruns for tuning Pulls column attributes from syscolumn, sysobjects table Tune to pull in other columns, attributes.

Confidential and proprietary materials for authorized Verizon personnel and outside agencies only. Use, disclosure or distribution of this material is not permitted to any unauthorized persons or third parties except by written agreement. 11

Data dictionary dashboard

Data source – consolidated table on “central” SQL Server Uses extract Refreshed as needed Published to Tableau Server Four components:

o Dictionary column list o Duplicate columns - bar o database size - bubble o server size - crosstab

Confidential and proprietary materials for authorized Verizon personnel and outside agencies only. Use, disclosure or distribution of this material is not permitted to any unauthorized persons or third parties except by written agreement. 12

Current benefits

Single source for information about our data Organized logically and clearly by SQL Server, database, table, column Updates take minutes for departmental overview Everyone is working from the same data Documents current data environment automatically Enables improved data management, often provides a glimpse of data linage Empowers enterprise level understanding and thinking