How to: Create New Database Objects Using Queries
If you prefer to use scripts to create or edit views, stored procedures, functions, triggers, or user-defined-types, you can use the Transact-SQL Editor. The Transact-SQL Editor provides IntelliSense and other language support. For more information, see Use Transact-SQL Editor to Edit and Execute Scripts.
The Transact-SQL Editor is invoked when you use the View Code contextual menu to open a database entity in a connected database or a project. It is also automatically opened when you use the New Query contextual menu from the SQL Server Object Explorer, or add a new script object to a database project. If you are not connected to a database but want to execute a query against it, you can also use the New Query Connection dialog box by selecting Transact-SQL Editor menu from the SQL menu to connect to a database and launch the Transact-SQL Editor.
|The following procedures use entities created in previous procedures in the Connected Database Development section.|
To create a new table using a Transact-SQL query
Right-click the Trade database node and select New Query .
In the script pane, paste in this code:
CREATE TABLE [dbo].[Fruits] ( [Id] INT NOT NULL, [Perishable] BIT DEFAULT ((1)) NULL, PRIMARY KEY CLUSTERED ([Id] ASC), FOREIGN KEY ([Id]) REFERENCES [dbo].[Products] ([Id]) );
Click the Execute Query button in the Transact-SQL Editor toolbar to run this query.
Right-click the Trade database in SQL Server Object Explorer and select Refresh . Notice that new Fruits table has been added to the database.
To create a new function
Replace the code in the current Transact-SQL Editor with the following:
CREATE FUNCTION [dbo].GetProductsBySupplier ( @SupplierId int ) RETURNS @returntable TABLE ( [Id] int NOT NULL, [Name] NVARCHAR (128) NOT NULL, [Shelflife] INT NOT NULL, [SupplierId] INT NOT NULL, [CustomerId] INT NOT NULL ) AS BEGIN INSERT @returntable SELECT * from Products p where p.SupplierId = @SupplierId RETURN END
This function will return all rows in the
SupplierIdequals to the specified parameter. Click the Execute Query button in the Transact-SQL Editor toolbar to run this query.
In SQL Server Object Explorer, under the Trade node, expand the Programmability and Functions nodes. You can find the new function you just created under Table-valued Functions .
To create a new view
Replace the code in the current Transact-SQL Editor with the following. Then click the Execute Query button above the editor to run this query.
CREATE VIEW [dbo].PerishableFruits AS SELECT p.Id, p.Name FROM dbo.Products p join dbo.Fruits f on f.Id = p.Id where f.Perishable = 1
In SQL Server Object Explorer, under the Trade node, expand the View node to locate the new view you just created.