Creating and viewing data for T-SQL Introduction

Create a database

IF DB_ID ('mydatabase6 ') IS NOT NULL

Drop database MyDatabase6;

Create database MyDatabase6

ON PRIMARY

(-- Primary data file

NAME = 'logicnameofdatafile6_1 ',

FILENAME

= 'C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/DataFile6_1.mdf ',

SIZE = 5 MB, -- set the initial SIZE

MAXSIZE = 100 MB, -- set the maximum storage space for a data file

FILEGROWTH = 15% -- set the automatic growth rate

),

FILEGROUP UserFG6_2 -- allocate the database file DataFile6_2.ndf to the file group UserFG6_2.

(-- Secondary data file

NAME = 'logicnameofdatafile6_2 ',

FILENAME

= 'C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/DataFile6_2.ndf ',

SIZE = 2 MB, -- set the initial SIZE

MAXSIZE = 100 MB, -- set the maximum storage space for a data file

FILEGROWTH = 10% -- set the automatic growth rate

),

FILEGROUP UserFG6_3 -- allocate the database file DataFile6_3.ndf to the file group UserFG6_3.

(-- Secondary data file

NAME = 'logicnameofdatafile6_3 ',

FILENAME

= 'C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/DataFile6_3.ndf ',

SIZE = 3 MB, -- set the initial SIZE

MAXSIZE = 100 MB, -- set the maximum storage space for a data file

FILEGROWTH = 5% -- set the automatic growth rate

)

Log on (

-- Set the logical name of the log file

NAME = 'logicnameoflogfile6 ',

-- Set the log file

FILENAME

= 'C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/LogFile6.ldf. ldf ',

SIZE = 10 MB, -- set the initial SIZE

MAXSIZE = 100 MB, -- set the maximum storage space for a data file

FILEGROWTH = 1 MB -- set the automatic growth rate

)

View Database

There are multiple ways to view a database. You can view system tables or use system stored procedures.

SELECT type_desc,

Name,

Physical_name,

Size,

Max_size,

Growth

FROM sys. database_files -- view system a. sys. database_files

GO

Sp_helpdb

The storage process sp_helpdb provides the ability to view basic database information. It is actually an encapsulation of several t-SQL commands.

These commands extract data from related system tables to obtain basic database information.

Syntax:

Sp_helpdb Database Name