Saturday, January 31, 2009

a small t-sql puzzle

A friend of mine found a small sql puzzle online somewhere and told me about that.I know it is not that hard to solve the problem using t-sql but I want to find out all the possible ways that I can to solve the puzzle.

Two tables
1.Users with their color assignments
2.All the possible colors.

Problem:
Find all the people that has all the colors in the second table.

Solutions:

Declare @usrs table(
[name] varchar(5),
Color varchar(10)
)
Declare @AllColors table(
Color varchar(10)
)
insert into @usrs values('a','red')
insert into @usrs values('a','green')
insert into @usrs values('a','blue')
insert into @usrs values('b','red')
insert into @usrs values('b','green')
insert into @usrs values('c','blue')
insert into @usrs values('c','red')
insert into @usrs values('c','green')
insert into @usrs values('d','blue')
insert into @usrs values('d','red')
insert into @usrs values('e','green')
insert into @usrs values('e','blue')


insert into @allColors values('red')
insert into @allColors values('green')
insert into @allColors values('blue')
--Solution1
Declare @AllColorsString Varchar(Max)
Select @AllColorsString = isnull(@AllColorsString,'')+ Color +','
From @AllColors
Order by Color ;
Print @AllColorsString;
With usrsNColors as
(
SELECT u1.name,
( SELECT Color + ','
FROM @usrs u2
WHERE u2.name = u1.name
ORDER BY color
FOR XML PATH('') ) AS ColorsAssigned
FROM @usrs u1
GROUP BY [name]
)
Select [Name],ColorsAssigned
From usrsNColors
Where ColorsAssigned = @AllColorsString;

--Solution2
SELECT DISTINCT Name, Colors
FROM @usrs u1
CROSS APPLY ( SELECT Color + ','
FROM @usrs u2
WHERE u2.Name = u1.Name
ORDER BY Color
FOR XML PATH('') ) D ( Colors )
Where Colors = @AllColorsString;
--Solution3
WITH CTE ( [name], colorList, ctecolor, length )
AS ( SELECT name, CAST( '' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ), 0
FROM @usrs
GROUP BY [name]
UNION ALL
SELECT p.[name], CAST( colorList +
CASE WHEN length = 0 THEN '' ELSE ', ' END + color AS VARCHAR(8000) ),
CAST( color AS VARCHAR(8000)), length + 1
FROM CTE c
INNER JOIN @usrs p
ON c.name = p.name
WHERE p.color> c.ctecolor )
select [name],colorList from cte
where length = (select count(*) from @AllColors)
Order by [name];

--Solution 4 (very similar to 3)
WITH CTE ( [name], colorList, ctecolor, length )
AS ( SELECT name, CAST( '' AS VARCHAR(100) ), CAST( '' AS VARCHAR(100) ), 0
FROM @usrs
GROUP BY [name]
UNION ALL
SELECT p.[name], CAST( colorList +
CASE WHEN length = 0 THEN '' ELSE ',' END + color AS VARCHAR(100) ),
CAST( color AS VARCHAR(100)), length + 1
FROM CTE c
INNER JOIN @usrs p
ON c.name = p.name
WHERE p.color> c.ctecolor )
select [name],colorList from cte
where colorList+',' = @AllColorsString
Order by [name];

--Solution5
With usrsNColors as
(
select row_number() over (partition by Name order by Color asc) as rownum, Name,Color
from @usrs
)
,ColorCntForUsr AS
(
Select Name,Max(rownum) as TotalColors From usrsNColors
Group By Name
)
Select * from ColorCntForUsr
Where TotalColors = (Select Count(*) from @AllColors);

--Solution6
select Name, Sum(case when a.color = u.color then 1 else 0 end) as matchCount from @usrs u
cross Join @AllColors a
Group by Name
Having Sum(case when a.color = u.color then 1 else 0 end) = (Select Count(*) from @AllColors);

--Solution7
Very similar to 6 but with a Inner Join.

Friday, January 30, 2009

OSQL - Running all the SQL Scripts in a given folder

Like I said in my earlier post OSQL is very useful to run sql scripts and run queries.This post demonstrates the use of OSQL in running all the scripts in a given folder.

@ECHO OFF
:: Keep variables local

SETLOCAL
:: Check command line arguments
SET Server=%1
SET Database=%2
SET Test=%3

IF NOT DEFINED Server
GOTO Syntax

IF NOT DEFINED Database GOTO Syntax

IF DEFINED Test GOTO Syntax

:: Execute sql scriptsFOR %%f IN (*.sql)
DO
(
:: ECHO Running script %%f... ECHO ^>^> %%f
OSQL -E -S %1 -d %2 -n -m-1 -b -i "%%f"
IF ERRORLEVEL 1
( ECHO.
ECHO ^>^> Critcal error in script %%f! Ending execution.
GOTO End
)
)
:: Display complete message
ECHO.
ECHO Finished running sql scripts on database %2

:: Done
GOTO End

:Syntax
ECHO.
ECHO Usage: RunSQLScripts server database scriptpath
GOTO End

:End
ENDLOCAL

OSQL - Running Queries in a Batch File

OSQL is another very useful tool I and my group use very often.We used it to execute all the sql files in a folder against a given server and database.

Here is the batch file I use to check if all the user procedures and user defined functions in a database has a execute persmission for a user ROLE called 'APPUSER'.

This sample batch file also demonstrates how to execute sql queries using OSQL in a batch file.


@ECHO OFF
:: Keep variables local
SETLOCAL

SET Query="select so.name from sys.objects so LEFT OUTER JOIN sys.database_permissions p on so.[object_id] = p.major_id inner JOIN sys.database_principals dp on dp.name = 'APPUSER' where so.Type IN ('P') and p.major_id is null"

ECHO ^>^> Running CheckIfAPPUSERIsGrantedExecuteOnAllProcs
ECHO ^>^> ----------------------------------------------
:serverset server=
set /P server=^>^> Enter Server Name or Type exit to Exit: %=%

if "%server%"=="" goto serverif "%server%"=="exit"
goto End

:databaseset database=
set /P database=^>^> Enter Database Name or Type exit to Exit: %=%
if "%database%"=="" goto databaseif "%database%"=="exit"
goto End

:: Execute sql scripts
ECHO ^>^> Checking . . . Please Wait!
OSQL -E -S %1 -d %2 -n -m-1 -b -Q %Query%

IF ERRORLEVEL 1
(
ECHO ^>^> Critcal error while checking Database %2,Ending Checking.
GOTO End
)

:: Display complete message
ECHO Check Complete on database %2
:: Done
GOTO End

:End
ENDLOCAL
pause
exit