Category Archives: T-SQL

Finding duplicates in SQL Server

Leave a reply

In a single column:

To get duplicates on a specific column, you can use the GROUP BY and COUNT(*) statements. For example, to find duplicates on the “ModifiedDate” column of the “Sales.SalesOrderDetails” table from the “AdventureWorks” sample database, you can use the following code:

SELECT ModifiedDate, COUNT(*) duplicates 
 FROM Sales.SalesOrderDetail
GROUP BY ModifiedDate
HAVING COUNT(*) > 1
ORDER BY COUNT(*) desc

Result:

In multiple columns:

Similarly, GROUP BY and COUNT(*) can be used too, but, in this case, we could convert each column to VARCHAR (so that they have all the same data type) and then concatenating them. For example, let’s use the “ModifiedDate” and “ProductID” fields from the same table:

select CONVERT(VARCHAR(100), ModifiedDate) + ' ' + CONVERT(VARCHAR(100),ProductID) columns,
  count(*) duplicates
from Sales.SalesOrderDetail
group by CONVERT(VARCHAR(100), ModifiedDate) + ' ' + CONVERT(VARCHAR(100),ProductID)
having count(*) > 1
order by count(*) desc

Result:

How to search for objects on all the SQL Server databases

Leave a reply

You can seach for objects in all the SQL Server databases within one instance by using the built-in stored procedure: sp_MSforeachdb. For example, let say that you are looking for a stored procedure called: uspGetManagerEmployees but you don’t know in what databases it was created. You can use the following 3 lines to find it:

DECLARE @command varchar(1000);
SET @command = 'USE ? select DB_NAME() dbname, p.* from sys.procedures p where name like ''%uspGetManagerEmployees%''';
EXEC sp_MSforeachdb @command;

Similarly, you can search for tables, columns, etc. For example, the following script is looking for a table called SalesPerson: 

DECLARE @command varchar(1000);
SET @command = 'USE ? SELECT c.* from INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME like ''SalesPerson'''; 
EXEC sp_MSforeachdb @command;

How to get the current running queries in SQL Server

Leave a reply

To get the list of current running queries, you can use the following script:

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
req.total_elapsed_time/1000 AS total_elapsed_time_in_secs,
req.total_elapsed_time/60000 AS total_elapsed_time_in_mins,
users.name
FROM sys.dm_exec_requests req
INNER JOIN sys.sysusers users on users.uid = req.user_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
order by sqltext.text

How to insert into a one column (identity) table

Leave a reply

Tables with just one column, which has been specified as identity columns, are not very common, and inserting data into them might be a bit tricky. To do that you need to use DEFAULT value without parenthesis. For example, let’s create the table IdentityTable:

CREATE TABLE IdentityTable
(
tableID INT PRIMARY KEY IDENTITY(1,1) NOT NULL
)

To insert data, you need to use the following statement:

INSERT INTO IdentityTable DEFAULT VALUES