Find all databases and physical path on SQL Server

Here's a very simple way to find all databases and their physical path on the server:

SELECT [name], [physical_name]

FROM sys.master_files

RESULT:

master        C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf

mastlog        C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

tempdev        C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdb.mdf

templog        C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\templog.ldf

modeldev    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\model.mdf

modellog    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\modellog.ldf

MSDBData    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf

...and so on

Feedback:

SELECT TOP N and BOTTOM N Rows Using SQL Server

If you have been looking out for a query that gives you TOP 'N' and Bottom 'N' records of a table, then check this out.

The query shown below selects the TOP 5 and BOTTOM 5 records ordered by CustomerID from the 'Customers' table of the Northwind database:

;With CTETemp

as

(

Select

CustomerID

,ROW_NUMBER() OVER (Order BY CustomerID) as TopFive

,ROW_NUMBER() OVER (Order BY CustomerID Desc) as BottomFive

FROM

Customers

)

Select CustomerID From CTETemp Where TopFive <=5 or BottomFive <=5

ORDER BY TopFive asc

Result:

Customer ID

ALFKI

ANATR

ANTON

AROUT

BERGS

WARTH

WELLI

WHITC

WILMK

WOLZA

Feedback:

Retrieve Records in Table A not present in Table B using SQL Server

Here's a query to retrieve records in Table A that are not present in Table B using SQL Server

SELECT tbla.ColumnA, tbla.ColumnB, tbla.ColumnC

FROM   TableA tbla

WHERE

  Not Exists(

        SELECT 1 FROM TableB tblb

        WHERE

          tbla.ColumnA = tblb.ColumnA 

          and tbla.ColumnB = tblb.ColumnB

          and tbla.ColumnC = tblb.ColumnC

         )

Feedback:

Find Names in the SQL Server Database with Special Characters

If your name fields has special characters like ~!@#$%^&*()-+":{} and so on, then here's a simple query that lets you find special characters in Names.

SELECT * FROM CustomersTemp

WHERE FirstName like '%[~!@%^&*()]%'

or LastName like '%[~!@%^&*()]%'

Feedback:

Programmatically adding an ID field to a table in SQL Server

We usually encounter a situation where we have to dynamically generate an ID columns based on some columns. The 'Customers' table of the Northwind database does not have an ID column. Let us see how to create and populate the ID column. The data will be ordered by the Country, City and Address columns.

First add a Column 'ID' to the table Customers

ALTER TABLE Customers

ADD ID int null

GO

Now execute the following code to insert data into the ID column based on some columns.

UPDATE temp

SET ID=Rowno

FROM

(

SELECT ID, ROW_NUMBER() OVER (ORDER BY [Country] asc, [City] asc, [Address] desc) AS Rowno

FROM Customers

)temp

The query shown above inserts data into the ID column based on the Country, City and Address fields

Feedback:

Compare Data Between Two Tables in SQL Server

I had explained the TableDiff in one of my previous posts.

In this post, let us see how to do a simple data comparison without using the TableDiff utility. Remember that this is only for small tables with less columns.

To create some sample data, let us take the Customers table of the Northwind database and create a duplicate of it

SELECT * INTO CustomersTemp FROM Customers

Now change some data in the Customers Temp table by using the following query

UPDATE CustomersTemp

SET City = 'Bern'

WHERE CUSTOMERID = 'ALFKI';

 

 

UPDATE CustomersTemp

SET Country = 'France'

WHERE CUSTOMERID = 'BOTTM';

It's now time to execute our query. I am comparing just 4 columns for changes. Remember as I said, use it on small tables with lesser columns:

SELECT table1.*, table2.*

FROM Customers AS table1

FULL JOIN CustomersTemp AS table2

ON table2.CustomerID = table1.CustomerID

WHERE

table1.CompanyName <> table2.CompanyName OR

table1.ContactName <> table2.ContactName OR

table1.Country <> table2.Country OR

table1.City <> table2.City

Running this query returns the two rows that do not match each other.

Know a better and simpler T-SQL that can do this? Share it here please!

Feedback:

Find Which Tables Have Triggers in SQL Server 2005/ 2008

If you want to quickly list down the tables having triggers in your database, here's how to do so:

USE [YourDB]

SELECT

OBJECT_NAME(parent_id) AS TableName,

name AS TriggerName,

create_date AS CreationDate,

modify_date AS ModifyDate

FROM sys.triggers

I ran the query on the PUBS database and got the following results:

TableName TriggerName      CreationDate              ModifyDate

employee  employee_insupd 2009-01-23 14:16:35.680 2009-01-23 14:16:35.680

Feedback:

Retrieve Last N Rows Based On a Condition in a SQL Server

I was recently working on a report where I had to display the Last 5 orders (by date) placed by an Employee. Here's how I got that data. This query will also work where the total number of records is less than 5:

I am using the Orders table of the Northwind database:

Let's first get all the records placed by Employee 5

SELECT ORDERID, CUSTOMERID, OrderDate

FROM Orders where EmployeeID=5

Order By OrderDate

Now let us retrieve the Last 5 orders placed by Employee 5. I just love the ROW NUMBER BY PARTITION Feature and here's how I will use it:

 

SELECT ORDERID, CUSTOMERID, OrderDate

FROM

(

SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY OrderDate DESC) AS OrderedDate,*

FROM Orders

) as ordlist

WHERE ordlist.EmployeeID = 5

AND ordlist.OrderedDate <= 5

Output:

ORDERID    CUSTOMERID    OrderDate

11043    SPECD    1998-04-22 00:00:00.000

10954    LINOD    1998-03-17 00:00:00.000

10922    HANAR    1998-03-03 00:00:00.000

10899    LILAS    1998-02-20 00:00:00.000

10874    GODOS    1998-02-06 00:00:00.000

Feedback:

How to Run Another Query if the first query does not Fetch results in SQL SERVER

Lets say you want to keep a back up query which should execute only when the first query does not return any results, then here's how to do so:

I have using the 'Customers' table from the Northwind database in this example

SELECT CompanyName, ContactName, ContactTitle FROM Customers

where CustomerID='XYZ'

-- Check if the first query returned results

-- If the ROWCOUNT is zero, run the other query

if @@ROWCOUNT = 0

BEGIN

SELECT CompanyName, ContactName, ContactTitle FROM Customers

where CustomerID='ALFKI'

END

Feedback:

Display TOP 'N' Last Modified Stored Procedure using SQL Server 2005/2008

In order to display a list of the TOP 5 Stored Procedure that were last modified, use this query

SELECT TOP 5 name, modify_date

FROM sys.procedures

ORDER BY modify_date DESC

On running this query on the Northwind database, the following results were obtained

Feedback: