i have these tables:

CREATE TABLE FirmDetails( firmFedID char(9) primary key , firmName varchar(50), firmAddress varchar(50) )

go

CREATE TABLE ProjectMain( projectId char(4) primary key, projectName varchar(50), firmFedID char(9) not null, activityId char(4) not null, projectTypeCode char(5), fundedbudget decimal(16,2), projectStartDate date, projectedEndDate date, projectStatus varchar(25), projectManager char(8),

)

go

CREATE TABLE ProjectType( projectTypeCode char(5) primary key, projectTypeDesc varchar(50) )

go

CREATE TABLE ActivityMain( activityId char(4) primary key, activityName varchar(50), costToDate decimal(16,2), activityStatus varchar(50), startDate date, endDate date,

)

go

CREATE TABLE StatusDescription( activityStatus varchar(50)primary key, projectStatus varchar(25)

)

go

How do i do this?

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