Tag Archives: Scripts

Finding Log Size for all Databases in SQL Server

Posted on by
Reply

Monitoring the size of Transaction Log files is one of those important tasks for a SQL Server Database Administrator. I monitor regularly in order to ensure that my database log files do not grow tremendously in size and potentially run out of space. The script in this article will give the list of Databases and their Transaction Log files size in MB in the descending order.

Script used in this blog…

SELECT INSTANCE_NAME AS [DATABASE],
(CNTR_VALUE/1000) AS Size_In_MB FROM MASTER.dbo.SYSPERFINFO
WHERE COUNTER_NAME LIKE '%Log File(s) Size (KB)%'
AND INSTANCE_NAME NOT IN ('_TOTAL','mssqlsystemresuorce')
ORDER BY Size_In_MB DESC

 You may also take a look at one of my previous blogs related to transaction logs. Find Transaction Log Space Used

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

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

Posted on by
Reply

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 YOURCOLUMN“.

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

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

Creating Primary Key in a SQL Server table

Posted on by
1

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 Key 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

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty