T-SQL Introduction to create and view data

Creating a Database

IF db_id (' MyDatabase6 ') is not NULL

DROP DATABASE MyDatabase6;

CREATE DATABASE MyDatabase6

On PRIMARY

(--Master information file

NAME = ' Logicnameofdatafile6_1 ',

FILENAME

= ' C:/Program files/microsoft SQL server/mssql10. Mssqlserver/mssql/data/datafile6_1.mdf ',

Size = 5MB,--Set Initial size

MAXSIZE = 100MB,--set maximum storage space for data files

FileGrowth = 15%--Set the automatic growth rate

)，

FILEGROUP Userfg6_2--Assigning a database file datafile6_2.ndf to a filegroup userfg6_2

(--Secondary information file

NAME = ' Logicnameofdatafile6_2 ',

FILENAME

= ' C:/Program files/microsoft SQL server/mssql10. Mssqlserver/mssql/data/datafile6_2.ndf ',

Size = 2MB,--Set Initial size

MAXSIZE = 100MB,--set maximum storage space for data files

FileGrowth = 10%--Set the automatic growth rate

)，

FILEGROUP Userfg6_3--Assigning a database file datafile6_3.ndf to a filegroup userfg6_3

(--Secondary information file

NAME = ' Logicnameofdatafile6_3 ',

FILENAME

= ' C:/Program files/microsoft SQL server/mssql10. Mssqlserver/mssql/data/datafile6_3.ndf ',

Size = 3MB,--Set Initial size

MAXSIZE = 100MB,--set maximum storage space for data files

FileGrowth = 5%--Set the automatic growth rate

)

LOG on (

--Set the logical name of the log file

NAME = ' LogicNameOfLogFile6 ',

--Set Log file

FILENAME

= ' C:/Program files/microsoft SQL server/mssql10. Mssqlserver/mssql/data/logfile6.ldf.ldf ',

Size = 10MB,--Set Initial size

MAXSIZE = 100MB,--set maximum storage space for data files

FileGrowth = 1MB--Set the automatic growth rate

)

View Database

There are several ways to view the database, you can view system tables, or you can 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

Stored procedure sp_helpdb provides the ability to view basic information about a database, which is actually an encapsulation of several T-SQL commands

These commands extract data from the related system tables and get basic database information.

The syntax is as follows

sp_helpdb database name