GROUP BY to combine/concat a column

Given the  table as follow:

ID  User  Activity  PageURL  
 1  Me    act1      ab     
 2  Me    act1      cd     
 3  You   act2      xy     
 4  You   act2      st

I want to group by User and Activity such that I end up with something like:

User  Activity  PageURL  
Me    act1      ab, cd     
You   act2      xy, st
SELECT
     [User], Activity,
     STUFF(
         (SELECT DISTINCT ',' + PageURL
          FROM TableName
          WHERE [User] = a.[User] AND Activity = a.Activity
          FOR XML PATH (''))
          , 1, 1, '')  AS URLList
FROM TableName AS a
GROUP BY [User], Activity

CROSS APPLY Explained

It could be used as a replacement for correlated sub queries and derived tables. That’s what we’ll discuss today.

 

SELECT
soh.SalesOrderID
,soh.OrderDate
,sod.max_unit_price
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
CROSS APPLY
(
SELECT
max_unit_price = MAX(sod.UnitPrice)
FROM Sales.SalesOrderDetail AS sod
WHERE soh.SalesOrderID = sod.SalesOrderID
) sod

 

read more…

Show database procceses

SET NOCOUNT ON
DECLARE @DBName VARCHAR(50)
SELECT @DBName = ‘TransferInventory’
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID(‘tempdb..#tmpSpwho2’))
DROP TABLE #tmpSpwho2

CREATE TABLE #tmpSpwho2 (SPID INT, Status VARCHAR(100), Login VARCHAR(100), HostName VARCHAR(100), Blkby VARCHAR(100), DBName VARCHAR(100), Command VARCHAR(50), CPUTime INT,
DiskIO INT, LastBatch VARCHAR(100), ProgramName VARCHAR(100), SPID2 INT, REQUESTID INT)

INSERT INTO #tmpSpwho2 EXEC sp_who2
SELECT ‘kill ‘ + CAST(SPID AS VARCHAR(5)) FROM #tmpSpwho2 WHERE DBName = @DBName

How to see sessions that are currently established in the database

set nocount on
DECLARE @DBName VARCHAR(50)
SELECT @DBName = ‘databaseName’
begin try
drop table #tmpSpwho2
end try
begin catch
end catch
CREATE TABLE #tmpSpwho2 (SPID INT, Status VARCHAR(100), Login VARCHAR(100), HostName VARCHAR(100), Blkby VARCHAR(100), DBName VARCHAR(100), Command VARCHAR(50), CPUTime INT,
DiskIO INT, LastBatch VARCHAR(100), ProgramName VARCHAR(100), SPID2 INT, REQUESTID INT)

INSERT INTO #tmpSpwho2 EXEC sp_who2
select * FROM #tmpSpwho2 WHERE DBName = @DBName

Insert rows from one table to another

WITH rates(Code, Rate, BaseCode, LastUpdate, [Auto], Source) 
 AS (SELECT crc.Code, crc.Rate, crc.BaseCode, crc.LastUpdate, crc.[Auto], crc.Source
  FROM CurrencyRateCurrent crc
  WHERE crc.AccountId = 7
  )

INSERT INTO [CurrencyRateCurrent] (Code, Rate, BaseCode, LastUpdate, [Auto], Source,AccountId)
SELECT Code, Rate, NULL, LastUpdate, [Auto], Source, 20 FROM rates

How to search all columns of all tables in a database for a keyword?

While browsing the SQL Server newsgroups, every once in a while, I see a request for a script that can search all the columns of all the tables in a given database for a specific keyword. I never took such posts seriously. But then recently, one of my network administrators was troubleshooting a problem with Microsoft Operations Manager (MOM). MOM uses SQL Server for storing all the computer, alert and performance related information. He narrowed the problem down to something specific, and needed a script that can search all the MOM tables for a specific string. I had no such script handy at that time, so we ended up searching manually.

That’s when I really felt the need for such a script and came up with this stored procedure “SearchAllTables”. It accepts a search string as input parameter, goes and searches all char, varchar, nchar, nvarchar columns of all tables (only user created tables. System tables are excluded), owned by all users in the current database. Feel free to extend this procedure to search other datatypes.

The output of this stored procedure contains two columns:

– 1) The table name and column name in which the search string was found
– 2) The actual content/value of the column (Only the first 3630 characters are displayed)

Here’s a word of caution, before you go ahead and run this procedure. Though this procedure is quite quick on smaller databases, it could take hours to complete, on a large database with too many character columns and a huge number of rows. So, if you are trying to run it on a large database, be prepared to wait (I did use the locking hint NOLOCK to reduce any locking). It is efficient to use Full-Text search feature for free text searching, but it doesn’t make sense for this type of ad-hoc requirements.

Create this procedure in the required database and here is how you run it:
CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN

— Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
— Purpose: To search all columns of all tables for a given search string
— Written by: Narayana Vyas Kondreddi
— Site: http://vyaskn.tripod.com
— Tested on: SQL Server 7.0 and SQL Server 2000
— Date modified: 28th July 2002 22:50 GMT

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ”
SET @SearchStr2 = QUOTENAME(‘%’ + @SearchStr + ‘%’,””)

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ”
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE’
AND QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)
), ‘IsMSShipped’
       ) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’, ‘nvarchar’)
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
‘SELECT ”’ + @TableName + ‘.’ + @ColumnName + ”’, LEFT(‘ + @ColumnName + ‘, 3630)
FROM ‘ + @TableName + ‘ (NOLOCK) ‘ +
‘ WHERE ‘ + @ColumnName + ‘ LIKE ‘ + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END

source: http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

SQL Server Optimization

This document is to describe different ways of improving the performance of SQL Server queries. Most of this document will describe index optimization, with occasional references to particular code snippets. In other words, this document will describe how to achieve the best performance, given the tables and queries to run against.
Database design issues and entity-relationship modeling techniques are out of scope of this document, even though flawed design can severely impact the performance in many ways.

Microsoft SQL Server 9.0 Technical Articles
SQL Server Optimization