Scripts

Dropping a Column from a table using T-SQL – SQL Server

May 30th

Posted by Brumedishetty in SQL Server

No comments

This blog explains how to drop a column from a table using T-SQL query. It might be easy to do it from SSMS, on the other side, it is not always convenient to do it from SSMS. When you have to repeat the task of dropping additional columns at a later time, you will have to go back to the object explorer repeatedly and choose those column to be dropped, where as using T-SQL query you just need to change the column name to be dropped. Now let’s look at the script to do it.

ALTER TABLE EmployeeTable
DROP COLUMN Gender 
GO 

 

The Syntax to drop a table column is quite simple, it goes this way “ALTER TABLE YOURTABLENAME DROP COLUMN YOURCOLUMNNAME“.

To use the code in this example, first you will need to create the table EmployeeTable using the script in another blog Creating Primary Key in a SQL Server table

Comments / feedback will be highly appreciated.

–Bru Medishetty

, ,

Creating Primary Key in a SQL Server table

May 24th

Posted by Brumedishetty in SQL Server

3 comments

In this blog we shall learn how to create a Primary Key on a table using T-SQL.

A Primary Key on a table can be either created at the time of creating the table or after the table is created by altering the table and adding the Primary Key Constraint.  This blog covers the first method of creating the table and Primary Key at the same time. The important change in the Create table script is to include a section inside the table schema definition by mentioning the keyword CONTRAINT followed by the Primary Key Name followed by the Ket words PRIMARY KEY.   

CREATE TABLE EmployeeTable
(
   EmployeeID INT , 
   FName VARCHAR (25), 
   LName VARCHAR (25), 
   Gender VARCHAR (1), 

   CONSTRAINT [PK_EMP_EMPID] PRIMARY KEY 

   (


      EmployeeID ASC 
   )
)ON [PRIMARY] 
GO 

Some points to remember about Primary Keys

  • A table can consist of one and only one Primary Key.
  • By default a clustered Primary Key is created.
  • A Primary Key on a table can be based on one or more columns of that table.
  • A Primary Key ensure no duplicate values can exist in that column(s).
  • A Primary Key on one column can be used as a parent key on another table and helps in ensuring referential integrity between those tables.
  • A Primary Key is required when creating a Transactional Publication in Replication.
 
 You can download the scripts used in this blog Here
, ,

DB_NAME() System Function in SQL Server

May 17th

Posted by Brumedishetty in SQL Server

No comments

In this blog, let’s take a look at a SQL Server System function, DB_NAME() .

This function is pretty handy when writing scripts that need you to identify the the Database name based on Database ID.

DB_NAME()

DB_NAME() function accepts an optional integer parameter (database_id) and returns the database name of that database_id, if no parameter is mentioned it returns the database name of the current database in whose context the script session is being executed. Let’s look at examples to understand it.

In the following picure, the function DB_NAME() returns the name of the database whose database_id is 1. Since the parameter value is passed on it returns the database name of that database_id, even though the query is running in the database SampleDataBase.

In the next example we look at how this function behaves when the optional parameter is not passed.

 

The result in this query is the database name in which the  query is executed. It returns the database name under whose contect the query is run, when there is a requirement to capture the database name through script, this can be used.

You may also want to take a look at this blog which explains about renaming the Database and the Database objects using T-SQL command. http://learnsqlwithbru.com/2010/03/01/renaming-database-and-database-objects-using-t-sql/

–Bru Medishetty

, , ,
123»