How To Get Database Diagram In Visual Studio 2012

This article gives an overview of SQL Schema and its usage in SQL Server.

Introduction to Schema

A SQL database contains multiple objects such as tables, views, stored procedures, functions, indexes, triggers. We define SQL Schema as a logical collection of database objects. A user owns that owns the schema is known as schema owner. It is a useful mechanism to segregate database objects for different applications, access rights, managing the security administration of databases. We do not have any restrictions on the number of objects in a schema.

Starting from SQL Server 2005, we have different meanings of user and schema. Now, the database object owner is a schema, and we define schema owners. We can have a single or multiple schema owners. It provides the following benefits:

• We can quickly transfer ownership of a SQL schema to another user
• We can share a schema among multiple users
• It allows you to move database objects among the schemas
• We get more control over database objects access and security

We define default SQL Schema for a database user in the create database user window:

If we do not define any default schema for a user, SQL Server assumes dbo as the default schema. We can verify the default schema for a user using the following system function:

.

Retrieve all schema and their owners in a database

We can query sys.schemas system table to find out schema in a database and their owners:

SELECT s . name AS schema_name , s . schema_id , u . name AS schema_owner FROM sys . schemas s INNER JOIN sys . sysusers u ON u . uid = s . principal_id ORDER BY s . name ;

In the following screenshot, we can see master database schema and their owners:

SQL Schema is a database entity, and you might get a different result of the query in a user database. For example, the AdventureWorks database shows the following schema information:

Note: SQL Server does not allow users to create objects in sys and INFORMATION_SCHEMA. It is used for storing internal system objects.

Let's create a new table in the AdventureWorks database using the following query:

CREATE TABLE DemoSchema ( ID INT IDENTITY ( 1 , 1 ) , Name VARCHAR ( 20 ) ) ;

We did not specify any schema in the CREATE TABLE statement. It automatically uses dbo schema for the table because the current user default schema is dbo:

Specify default SQL schema while creating a new login

Let's create a new SQL login and database user with a default schema other than dbo.

Go to Security and create a new login:

Provide the following inputs on the general page:

1. Enter a SQL user name
2. Choose SQL Server authentication method and enter a password for the user

Navigate to the User Mapping page and put a check on the AdventureWorks database:

Click on eclipse for default schema and browse available schema in the database. Let's select schema [Person] as a default schema for this user:

Click OK and return to the user-mapping page. We can see that the new login is having access to the AdventureWorks database and its default schema is [Person]:

Click OK, and it creates the SQL login, database user in the AdventureWorks database. Connect to SQL instance with this login. Execute the create table statement without specifying a SQL Schema. We can see it creates the table in the default schema for the user, i.e. [Person]:

Alternatively, we can run the script by specifying a schema name.

Script to create a table in [Person] schema:

CREATE TABLE [ person ] . [ DemoSchema ] ( ID INT IDENTITY ( 1 , 1 ) , Name VARCHAR ( 20 ) ) ;

Script to create a table in the dbo schema:

CREATE TABLE [ dbo ] . [ DemoSchema ] ( ID INT IDENTITY ( 1 , 1 ) , Name VARCHAR ( 20 ) ) ;

Create a new SQL Schema in SQL Server

We can create schemas as per our requirements. We can create a schema in a specific database. Let's create a new schema in the AdventureWorks database.

Expand the AdventureWorks database and go to Security:

Right-click on Schemas and click to New Schema:

Specify a new schema name and schema owner. You can search the schema owner from existing logins:

Instead of creating the schema, click on generate scripts to give you an equivalent T-SQL statement:

USE [ AdventureWorks ] GO CREATE SCHEMA [ Customer ] AUTHORIZATION [ Demouser ] GO

In this syntax, we can see that:

• [Customer] is the new schema name that we want to create in the AdventureWorks database
• We specify a schema owner using the AUTHORIZATION keyword in the CREATE SCHEMA command

Execute the command and refresh the schema list (right-click on schemas and refresh). You can see a new schema [Customer] on this list:

Note: Earlier, we created a demo user with a default schema [Person]. The demo user is the schema owner of the customer schema as well. If we connect to SQL instance using the demo user and create objects without specifying a schema name, it still takes default schema as [Person].

Change SQL schema of an existing object in SQL Server

We can transfer objects to different schemas, as well. For example, let's migrate a table from [dbo] owner to sales schema in the AdventureWorks database following these steps:

1. Right-click on the specific table name and choose Design option:

   

2. It opens the table designer. We can change table properties such as column data types, default values, schema using this designer. Click on Properties as shown in the following image:

   

   It opens the table properties. It shows the table schema, server name, identity column, lock escalation, and filegroup information:

   

3. Click on SQL Schema, and it opens the available scheme in the database:

   

4. Select the required schema [Sales], and it gives the warning:

   Changing the schema of this object will result in all current permissions on this object being dropped

5. Click on Yes to proceed:

   

6. Close the table designer and save the changes after clicking on Yes:

   

7. Refresh the tables in the database, and we can see the schema of this table changes from dbo to sales:

   

By default, SQL Server searches for the object in the default schema and dbo schema. If the object belongs to other than the default and dbo schema, we require to specify schema name while accessing the object. You get an error message if the object does not exist in the default or dbo schema:

We can specify a schema name in the following format to access an object.

In this command, [sales] is a schema and [Demoschema] is a table:

SELECT * FROM sales . DemoSchema ;

Conclusion

A SQL schema is a useful database concept. It helps us to create a logical grouping of objects such as tables, stored procedures, and functions.

• Author
• Recent Posts

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date.

He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious "Best author of the year" continuously in 2020 and 2021 at SQLShack.

Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Source: https://www.sqlshack.com/a-walkthrough-of-sql-schema/

Posted by: alinealinedevrieseer10265219.blogspot.com