Most of the articles that I presented so far for database development have been centered around the exact Microsoft T-SQL syntax to perform a certain operation. At the University of Rhode Island, I learnt how to program in Pascal before typing in my first ANSI SQL statement. In short, I will always be some type of developer in my heart.
However, many people do not come from a computer science and math background. The SQL Server Management Studio (SSMS) has menus and dialog boxes to achieve many of the same results.
Today, I am going to continue the series of articles that will recreate the [AUTOS] sample database while demonstrating how common T-SQL tasks in my script can be done with SSMS. I will be going over sections 1E in the sample code.
I want to explore how the CREATE SCHEMA, ALTER SCHEMA, and DROP SCHEMA data definition language (DDL) constructs can be achieved with SSMS.
A user defined schema allows a database administrator (DBA) to define security on groups of objects. I think this is a very powerful but under utilized technique.
The security node under the [AUTOS] database in the object explorer tree shows the default schema for any database. As we know, every newly created object defaults to the [dbo] schema if not explicitly defined. Exceptions to this rule are when a default schema other than [dbo] is selected when creating a database user.
I want to execute the CREATE SCHEMA statement twice in the [AUTOS] database: one for [ACTIVE] data and one for [STAGE] data.
The first step is to right click the security node in the object explorer and select the New Schema menu option.
The second step is to choose a name and owner for the new schema. I usually default schema ownership to [dbo].
Repeat these steps again for the [STAGE] schema.
Taking another look at the security node under the [AUTOS] database in the object explorer show our two new SCHEMA.
What is the use of a schema without declaring a member object?
I am jumping ahead in our series of SSMS talks to table creation. Just run the T-SQL code in Section 1L to create the [MODELS] table on the [STAGE] schema. Please see screen shot of table below.
Now, let’s see what happens when we try to execute the DROP SCHEMA statement on a schema that is bound to an object.
Find the security node in the object explorer and select the [STAGE] schema. Right click and choose the delete menu option. This will bring up the delete object dialog box. Clicking OKAY will try to remove the schema.
The following error occurs when we try this action. In short, the schema must be empty before we try this operation. I will leave the task of dropping the [MODELS] table then the [STAGE] schema as an exercise that you can do.
In summary, we can do many of the Data Definition Language (DDL) tasks that can be done in T-SQL. The cool script button shows you what SQL Server Management Studio (SSMS) will use for code to accomplish a particular task. Next time, I will be talking about creating server logins and database users with SSMS.
