Help Please?

Step 2: DBA Exercise: Create System Catalog Scripts

For Step2 and Step 3, make sure you review the Database Health Monitoring Doument Using the System Catalog Views (known as Object Catalog Views in SQL Server) https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/object-catalog-views-transact-sql create the SQL Scripts for the following views:

1. vw_TableNoIndexes: User tables with no Indexes

2. vw_ProjectIdTables: All the tables which contain the column projectId

3. vw_Last7Obj: All the objects that have been modified in the last 7 days

4. vw_ProjectProcs: The SQL logic from the stored procedures which have Project in their name

Step 3: DBA Troubleshooting

Create the following Stored Procedures to assist in performing a database administrator tasks.

Sp_ActiveConnections Return all the active connections for a given database name. Parameters: @databasename varchar(250)

Hint: Use sys.sysprocesses.

You should do some grouping to a count of the connections.

Output:

DatabaseName NumberOfConnections LoginName

COP4203 1 aproquel

Sp_LogFileStatus

Return all the status of all the transaction log files for a given database name and their size in kilobytes. Parameters: @databasename varchar(250) LogSize: Size of the log file DataSize: Size of the data file

Hint: Use sys.master_files from the Database Health Monitoring document. Output:

DatabaseName LogSize DataSize

COP4203 12168 73728