Ques 46. How To Add a New Column to an Existing Table with "ALTER TABLE ... ADD" in MS SQL Server?
If you have an existing table with existing data rows, and want to add a new column to that table, you can use the "ALTER TABLE ... ADD" statement. The tutorial script below shows you a good example:
ALTER TABLE test ADD author VARCHAR(40)
GO
sp_columns test
GO
TABLE_OWNER TABLE_NAME COLUMN_TABLE TYPE_NAME ...
dbo test id int ...
dbo test subject varchar ...
dbo test description varchar ...
dbo test create_date datetime ...
dbo test author datetime ...
SELECT * FROM test
GO
id subject description create_date author
This SQL script added a new column called "author" to the "test" table. NULL values were added to this column on all existing data rows.
Is it helpful?
Add CommentView Comments
Ques 47. How to create a view and a stored procedure in MS SQL Server using "CREATE VIEW/PROCEDURE" statements?
This answer is about creating login and configure users for databases with Transact-SQL statements. Granting a user access to a database involves three steps. First, you create a login. The login lets the user connect to the SQL Server Database Engine. Then you configure the login as a user in the specified database. And finally, you grant that user permission to database objects. This lesson shows you these three steps, and shows you how to create a view and a stored procedure as the object. This tutorial assumes that you are running SQL Server Management Studio Express.
Now that Mary can access the YourDataBaseName database, you may want to create some database objects, such as a view and a stored procedure, and then grant Mary access to them. A view is a stored SELECT statement, and a stored procedure is one or more Transact-SQL statements that execute as a batch.
Views are queried like tables and do not accept parameters. Stored procedures are more complex than views. Stored procedures can have both input and output parameters and can contain statements to control the flow of the code, such as IF and WHILE statements. It is good programming practice to use stored procedures for all repetitive actions in the database.
Is it helpful?
Add CommentView Comments
Ques 48. How to grant a permission in MS SQL Server using "GRANT EXECUTE" statements?
This answer is about creating login and configure users for databases with Transact-SQL statements. Granting a user access to a database involves three steps. First, you create a login. The login lets the user connect to the SQL Server Database Engine. Then you configure the login as a user in the specified database. And finally, you grant that user permission to database objects. This answer shows you these three steps, and shows you how to create a view and a stored procedure as the object. This tutorial assumes that you are running SQL Server Management Studio Express.
As an administrator, you can execute the SELECT from the Products table and the vw_Names view, and execute the pr_Names procedure; however, Mary cannot. To grant Mary the necessary permissions, use the GRANT statement.
Procedure Title - Execute the following statement to give Mary the EXECUTE permission for the pr_Names stored procedure.
GRANT EXECUTE ON pr_Names TO Mary;
GO
Is it helpful?
Add CommentView Comments
Ques 49. How to delete database objects with "DROP" statements in MS SQL Server?
To remove all database objects created by previous tutorials, you could just delete the database. However, in this tutorial, you will go through the steps to reverse every action you took doing the tutorial.
Removing permissions and objects - Before you delete objects, make sure you are in the correct database:
USE YourDataBaseName;
GO
Use the REVOKE statement to remove execute permission for Mary on the stored procedure:
REVOKE EXECUTE ON pr_Names FROM Mary;
GO
Use the DROP statement to remove permission for Mary to access the YourDataBaseName database:
DROP USER Mary;
GO
Use the DROP statement to remove permission for Mary to access this instance of SQL Server 2005:
DROP LOGIN [Mary];
GO
Use the DROP statement to remove the store procedure pr_Names:
DROP PROC pr_Names;
GO
Use the DROP statement to remove the view vw_Names:
DROP View vw_Names;
GO
Use the DELETE statement to remove all rows from the Products table:
DELETE FROM Products;
GO
Use the DROP statement to remove the Products table:
DROP Table Products;
GO
You cannot remove the YourDataBaseName database while you are in the database; therefore, first switch context to another database, and then use the DROP statement.
Is it helpful?
Add CommentView Comments
Ques 50. What is a database in MS SQL Server?
A database is a logical container that contains a set of related database objects:
* Tables - Storages of structured data.
* Views - Queries to present data from tables.
* Indexes - Sorting indexes to speed up searches.
* Stored Procedures - Predefined SQL program units.
* Users - Identifications used for data access control.
* Other objects.