Stored Procedures as a DBA in Visual Studio .NET

Creation QR Code in Visual Studio .NET Stored Procedures as a DBA

Stored Procedures as a DBA
Paint QR In .NET
Using Barcode generation for .NET Control to generate, create QR image in Visual Studio .NET applications.
QR Code ISO/IEC18004 Scanner In VS .NET
Using Barcode decoder for VS .NET Control to read, scan read, scan image in .NET framework applications.
While the previous exercises help you to get your hands on both stored procedures and functions, you may like to see something that is useful on the job We explore DBCC CheckDB a little more in 7, but for now be aware that it is a very useful utility to let us know about the health of any databases we check In the following exercise, we ll create a stored procedure to run this on all of our databases Exercise 54: Creating a Stored Procedure to Check Database Integrity 1 Open a New Query window in SSMS 2 Execute the following script to check the health of the AdventureWorks database This script takes about 20 or more seconds to run
Barcode Generation In .NET Framework
Using Barcode encoder for .NET framework Control to generate, create bar code image in .NET applications.
Decoding Barcode In .NET
Using Barcode decoder for .NET framework Control to read, scan read, scan image in Visual Studio .NET applications.
USE AdventureWorks; GO DBCC CHECKDB;
Create QR-Code In Visual C#
Using Barcode generator for Visual Studio .NET Control to generate, create Denso QR Bar Code image in .NET framework applications.
QR Code Creation In VS .NET
Using Barcode generator for ASP.NET Control to generate, create Denso QR Bar Code image in ASP.NET applications.
3 As part of a maintenance plan, you may choose to create stored procedures that you will use on a regular basis Instead of creating these in user databases or system databases, it s considered a best practice to create these objects in your own database Create a database named Dba to hold your database administrator objects with the following script:
Denso QR Bar Code Encoder In Visual Basic .NET
Using Barcode creation for .NET framework Control to generate, create Quick Response Code image in .NET framework applications.
EAN / UCC - 13 Creation In Visual Studio .NET
Using Barcode encoder for .NET Control to generate, create USS-128 image in .NET applications.
Use Master; GO CREATE DATABASE Dba;
Creating Linear 1D Barcode In Visual Studio .NET
Using Barcode generation for .NET framework Control to generate, create 1D Barcode image in VS .NET applications.
Generating Matrix Barcode In VS .NET
Using Barcode encoder for .NET framework Control to generate, create Matrix 2D Barcode image in .NET framework applications.
4 Execute the following script to identify how many databases you have in your system:
EAN-13 Creator In Visual Studio .NET
Using Barcode drawer for VS .NET Control to generate, create GTIN - 13 image in .NET applications.
Creating International Standard Serial Number In .NET
Using Barcode maker for Visual Studio .NET Control to generate, create ISSN image in .NET applications.
SELECT MAX(dbid) FROM masterdbosysdatabases;
Code-128 Creation In Visual C#
Using Barcode printer for Visual Studio .NET Control to generate, create Code 128 Code Set B image in VS .NET applications.
Code 3/9 Creation In Java
Using Barcode creation for Java Control to generate, create Code-39 image in Java applications.
MCITP SQL Server 2005 Database Administration All-in-One Exam Guide
Barcode Recognizer In VB.NET
Using Barcode Control SDK for Visual Studio .NET Control to generate, create, read, scan barcode image in .NET applications.
Barcode Maker In None
Using Barcode generation for Software Control to generate, create barcode image in Software applications.
We can use this statement embedded into a script instead of the actual number of databases 5 What we need to do is run DBCC CHECKDB on each database However, there could easily be too many to do manually By creating a script, we can automate it Enter the following script to build the DBCC CHECKDB statement for each database:
Generating GTIN - 128 In Java
Using Barcode creation for Java Control to generate, create GS1 128 image in Java applications.
Paint DataMatrix In Objective-C
Using Barcode generation for iPad Control to generate, create Data Matrix image in iPad applications.
--Declare variables DECLARE @dbid integer; --Current database DECLARE @DBName nvarchar(50); --Database name DECLARE @mySQL nvarchar(200); --SQL Statement --Start with first database SET @dbid = 1; --Loop through all databases WHILE @dbid < (SELECT MAX(dbid) FROM masterdbosysdatabases) BEGIN SELECT @DBName = name FROM masterdbosysdatabases WHERE dbid = @dbid; --Dynamically build statement to execute for each database SET @mySQL = 'DBCC CHECKDB(' + @DBName + ')'; --Show dynamically built SQL statement SELECT 'Statement = ' + @mySQL AS 'Dynamically Built T-SQL Statement'; --Increment database to do the same for the next database Set @dbid = @dbid + 1; END;
Drawing Code39 In None
Using Barcode printer for Office Word Control to generate, create Code 3/9 image in Word applications.
UPC Symbol Maker In Java
Using Barcode printer for Java Control to generate, create GTIN - 12 image in Java applications.
When you run this script, you ll notice that the SELECT statement that is output just shows us what the dynamically built statement looks like It doesn t run it Also notice that we re using the + to concatenate the string 'DBCC CHECKDB(' with the actual name of the database pulled from the SELECT statement 6 Add the EXEC line (in bold next) after the SELECT statement:
SELECT 'Statement = ' + @mySQL AS 'Dynamically Built T-SQL Statement'; EXEC sp_executesql @statement = @mySQL
7 Execute the modified script This will take a while Afterward, you should see a result set similar to Figure 5-5
Figure 5-5 Results output of script
5: Advanced Database Objects
8 Click the Messages tab This allows you to see the results of the CheckDB statement It should look similar to Figure 5-6
Figure 5-6 Messages from the CheckDB statement
9 We used the SELECT statement for debugging as we built the script, but we don t need it in the stored procedure, so remove it by adding two dashes to the beginning to comment it out, as shown next Rerun your script again to ensure it still works correctly
--SELECT 'Statement = ' + @mySQL AS 'Dynamically Built T-SQL Statement';
10 Now make this into a stored procedure Add the bolded lines to the beginning of your script Execute the script to create the stored procedure
USE Dba; GO CREATE PROC usp_DBCC_CheckAllDB AS --Declare variables DECLARE @dbid integer; DECLARE @DBName nvarchar(50); DECLARE @mySQL nvarchar(200); --Start with first database SET @dbid = 1; --Loop through all databases WHILE @dbid < (SELECT MAX(dbid) FROM masterdbosysdatabases) BEGIN SELECT @DBName = name FROM masterdbosysdatabases WHERE dbid = @dbid; --Dynamically build statement to execute for each database SET @mySQL = 'DBCC CHECKDB(' + @DBName + ')'; --SELECT 'Statement = ' + @mySQL AS 'Dynamically Built T-SQL Statement'; EXEC sp_executesql @statement = @mySQL --Increment database to do the same for the next database Set @dbid = @dbid + 1; END;
11 Execute the following script to execute your stored procedure:
Copyright © OnBarcode.com . All rights reserved.