Archive

Posts Tagged ‘SQL 2008 R2’

SQL Server – Different Ways to Check Object Definition

14.04.2014 2 comments

sp_helptext is widely used for checking object definition in SQL Server. sp_helptext can be used to check definition of various database objects like Views, Stored Procedures and User Defined Functions.

There are two other options which can be used to retrieve object definition:

OBJECT_DEFINITION( object_id ) – is a built-in function. It can also retrieve definitions of CHECK/DEFAULT constraints

sys.sql_modules – is a catalog view which returns definitions of all modules in current database

Each of these can be used as follows:


USE [SqlAndMe]
GO

sp_helptext 'MyProcedure'
GO

-- Use OBJECT_ID() function to get object id
SELECT    OBJECT_DEFINITION(OBJECT_ID('MyProcedure'))
GO

-- Use OBJECT_ID() function to get object id
SELECT    [definition]
FROM    sys.sql_modules
WHERE    object_id = OBJECT_ID('MyProcedure')
GO

OBJECT_DEFINITION(object_id) and sys.sql_modules returns results as a single-line when in “Results to Grid” (Ctrl + D) mode. Switch to “Results to Text” (Ctrl + T) for formatted output which will include line breaks.

Hope This Helps!

Vishal

SQL Server – How to get last access/update time for a table

11.03.2014 2 comments

Modify date and create date for a table can be retrieved from sys.tables catalog view. When any structural changes are made the modify date is updated. It can be queried as follows:


USE [SqlAndMe]
GO

SELECT    [TableName] = name,
create_date,
modify_date
FROM    sys.tables
WHERE    name = 'TransactionHistoryArchive'
GO

 

 

sys.tables only shows modify date for structural changes. If we need to check when was the tables last updated or accessed, we can use dynamic management view sys.dm_db_index_usage_stats. This DMV returns counts of different types of index operations and last time the operation was performed.

It can be used as follows:


USE [SqlAndMe]
GO

SELECT    [TableName] = OBJECT_NAME(object_id),
last_user_update, last_user_seek, last_user_scan, last_user_lookup
FROM    sys.dm_db_index_usage_stats
WHERE    database_id = DB_ID('SqlAndMe')
AND        OBJECT_NAME(object_id) = 'TransactionHistoryArchive'
GO

 

 

last_user_update – provides time of last user update

last_user_* – provides time of last scan/seek/lookup

It is important to note that sys.dm_db_index_usage_stats counters are reset when SQL Server service is restarted.

 

Hope This Helps!

Vishal

SQL Server – SELECTing/Displaying Top N rows from a table

15.07.2013 No comments

To SELECT only top N rows from a table we can use TOP clause in SELECT statement. Using TOP clause we can also specify percentage option.

For example, both of these statements are valid:

USE SqlAndMe

GO

 

SELECT TOP 5 EmployeeId,FirstName,LastName

FROM   dbo.Table_Employees

GO

 

SELECT TOP 5 PERCENT EmployeeId,FirstName,LastName

FROM   dbo.Table_Employees

GO

EmployeeId    FirstName     LastName

1             Ken           Sánchez

2             Terri         Duffy

3             Gail          Erickson

4             Ken           Sánchez

5             Terri         Duffy

 

(5 row(s) affected)

 

 

 

EmployeeId    FirstName     LastName

1             Ken           Sánchez

 

(1 row(s) affected)

Another way to limit rows in result set is to use SET ROWCOUNT N statement. SET ROWCOUNT N stops processing the query after specified number of rows are returned.

It can be used as below:

USE SqlAndMe

GO

 

SET ROWCOUNT 5

GO

 

SELECT EmployeeId,FirstName,LastName

FROM   dbo.Table_Employees

GO

 

SET ROWCOUNT 1

GO

 

SELECT EmployeeId,FirstName,LastName

FROM   dbo.Table_Employees

GO

EmployeeId    FirstName     LastName

1             Ken           Sánchez

2             Terri         Duffy

3             Gail          Erickson

4             Ken           Sánchez

5             Terri         Duffy

 

(5 row(s) affected)

 

 

EmployeeId    FirstName     LastName

1             Ken           Sánchez

 

(1 row(s) affected)

Using SET ROWCOUNT N affects current session, to turn off this setting we need to set it to 0.

SET ROWCOUNT 0

GO

There are some differences between using SET ROWCOUNT N and TOP N [PERCENT]. You can read about these differences here: SET ROWCOUNT VS TOP in SQL Server

Hope This Helps!

Vishal

SQL Server – How to change SQL Server ERRORLOG location

08.07.2013 3 comments

By default SQL Server ERRORLOG is stored in "C:Program FilesMicrosoft SQL ServerInstanceFolderMSSQLLog" folder. The ERRORLOG location is configured as a startup parameter for SQL Server Service.

image

To change the location of ERRORLOG you need to modify the startup parameter -e.

For example, if you need to move logs to C:Logs then replace the startup parameter as below:

Step 1:

Replace "-eC:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLLogERRORLOG"

to "-eC:LogsERRORLOG"

Step 2:

Restart SQL Server Service to apply changes. The ERRORLOG will be created to "C:Logs" after SQL Server is restarted.

 

To change location for SQL Server Agent Logs:

Location for SQL Server Agent Log can be changed using T-SQL or SQL Server Management Studio.

Method 1: To change SQL Server Agent Log location right click on "Error Logs" node and select "Configure"

image

Provide new location for Log file in "Error Log File" path:

image

Click on "OK" and restart SQL Server Agent to apply changes.

Method 2: To change log location using T-SQL

To change log location using T-SQL you can use below code:

USE [msdb]

GO

EXEC   msdb.dbo.sp_set_sqlagent_properties

       @errorlog_file = N'C:LogsSQLAGENT.OUT'

GO

Execute this code and restart SQL Server agent to apply changes.

Hope This Helps!

Vishal

SQL Server – Update Table with INNER JOIN

18.06.2013 1 comment

Often we may need to update a column in a table based of another column in another table.

In SQL Server you can do this using UPDATE statement by joining tables together.

To understand this better let’s take a look at below contrived example.

USE [SqlAndMe]

GO

 

SELECT CustomerID, Name, OrderAmount

FROM   dbo.Customers

GO

 

SELECT OrderID, CustomerID, Amount

FROM   dbo.Orders

GO

Result Set:

CustomerID    Name          OrderAmount

3             Curtis        NULL

4             Lanna         NULL

5             Marlin        NULL

6             Henry         NULL

 

(4 row(s) affected)

 

OrderID       CustomerID    Amount

107           6             8745.00

123           5             4582.00

643           3             5693.00

 

(3 row(s) affected)

In the above data I want to update OrderAmount column of dbo.Customers with values from Amount column of dbo.Orders.

To achieve this we can use UPDATE statement as below:

UPDATE CUST

SET    CUST.OrderAmount = ORDR.Amount

FROM   dbo.Customers CUST

INNER JOIN dbo.Orders ORDR ON CUST.CustomerID = ORDR.CustomerID

GO

 

(3 row(s) affected)

OrderAmount column in dbo.Customers Table is now updated based on JOIN condition.

SELECT CustomerID, Name, OrderAmount

FROM   dbo.Customers

GO

CustomerID    Name          OrderAmount

3             Curtis        5693.00

4             Lanna         NULL

5             Marlin        4582.00

6             Henry         8745.00

 

(4 row(s) affected)

 

I have used a simple example here to explain the concept easily

Hope This Helps!

Vishal

SQL Server – How to get sizes of all databases on a server

10.06.2013 7 comments

To get database size information we can use sys.master_files catalog view. This view contains a row per file of a database for all databases.

The columns of interest for retrieving database size information are:

Column Name Description
database_id ID of the database to which the file belongs to
type_desc Description of file type. It can be ROWS, LOG, FILESTREAM or FULLTEXT
size File size in number of 8KB pages

Using this information we can retrieve database sizes using below query:

SELECT [Database Name] = DB_NAME(database_id),

       [Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'

                     WHEN Type_Desc = 'LOG'  THEN 'Log File(s)'

                     ELSE Type_Desc END,

       [Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )

FROM   sys.master_files

— Uncomment if you need to query for a particular database

— WHERE      database_id = DB_ID(‘Database Name’)

GROUP BY      GROUPING SETS

              (

                     (DB_NAME(database_id), Type_Desc),

                     (DB_NAME(database_id))

              )

ORDER BY      DB_NAME(database_id), Type_Desc DESC

GO

Result Set:

 Database Name        Type                 Size in MB

AdventureWorks2012   Data File(s)         189.00

AdventureWorks2012   Log File(s)          0.75

AdventureWorks2012   NULL                 189.75

Credit               Data File(s)         170.94

Credit               Log File(s)          10.00

Credit               NULL                 180.94

master              Data File(s)          4.00

master              Log File(s)           0.75

master              NULL                  4.75

model                Data File(s)         3.06

model                Log File(s)          0.75

model                NULL                 3.81

msdb                 Data File(s)         16.69

msdb                 Log File(s)          19.63

msdb                 NULL                 36.31

Northwind            Data File(s)         4.25

Northwind            Log File(s)          3.06

Northwind            NULL                 7.31

pubs                 Data File(s)         3.25

pubs                 Log File(s)          3.06

pubs                 NULL                 6.31

SqlAndMe             Data File(s)         137.88

SqlAndMe             Log File(s)          19.13

SqlAndMe             NULL                 157.00

tempdb              Data File(s)          8.00

tempdb              Log File(s)           0.50

tempdb              NULL                  8.50

 

(27 row(s) affected)

The above query gets sizes for Data Files and Log Files and displays a total using GROUPING SETS.

Hope This Helps!

Vishal

SQL Server – How to Detach a Database

03.06.2013 No comments

To move a database from one location to other you fist need to detach the database from server. In this article we will learn different ways to detach a database from server.

There are two different methods available to detach a database from a server.

Method 1. Detach a Database using "Detach" Task in SQL Server Management Studio:

To detach a database using SQL Server Management Studio:

1. Right Click on Database you want to detach and Select "Tasks" > "Detach" Option.

image

2. Check the "Drop Connections" checkbox and click "OK" to detach the database

image

Now the database is detached from server and you can move .mdf and .ldf files related to database.

 

Method 2: Detach database using T-SQL:

To detach a database from a server using T-SQL you can use below code:

USE [master]

GO

 

ALTER DATABASE [SqlAndMe] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

EXEC master.dbo.sp_detach_db @dbname = N'SqlAndMe'

GO

Result Set:

Command(s) completed successfully.

This detaches database from server.

Hope This Helps!

Vishal