How to find out the recovery model for all databases on SQL Server 2000, 2005 and 2008

How do you find out the recovery model for all the databases on your SQL Server box?
On a SQL Server 2005/2008 box you can use the sys.databases view, the sys.databases view returns a column named recovery_model_desc.
On a SQL server 2000 box you will have to use the DATABASEPROPERTYEX function. The 2000 version will also work on 2000 and 2008 (I tested this with the July CTP)


--2005/2008 version
SELECT [name],
recovery_model_desc
FROM sys.databases


--2000/2005/2008 version
SELECT [name],
DATABASEPROPERTYEX([name],'Recovery') AS recovery_model_desc
FROM master..sysdatabases

Do you know how NULLIF and non-deterministic functions work?

Run this first

CREATE TABLE #j (n varchar(15))

DECLARE @a int
SET @a = 1
WHILE @a <= 1000
BEGIN
INSERT #j
SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ')
SET @a = @a + 1
END
Go

After that is done run this query

SELECT * FROM #j WHERE n = ' '

You will get back between 200 and 300 rows. What just happened? In our insert we use this NULLIF(REPLICATE('1', RAND()*2) , ' ')
What this does is the following: if REPLICATE('1', RAND()*2) equals ' ' then it will insert a NULL, so where do the blanks come from? Well let's find out

run this

SET SHOWPLAN_TEXT ON

SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ')

and we see the following
--Compute Scalar(DEFINE:([Expr1000]=If (replicate('1', Convert(rand(NULL)*2))=' ') then NULL else replicate('1', Convert(rand(NULL)*2))))

This can also be written like this

SELECT CASE WHEN REPLICATE('1', RAND()*2) =' '
THEN NULL ELSE REPLICATE('1', RAND()*2) END

See what happens? First SQL evaluates if REPLICATE('1', RAND()*2) is ' ' if that is not ' ' then the same code gets executed again and thus could be a blank
If you use a variable this can never happen.

Here is an example, keep hitting F5 and you will see it will never be blank

DECLARE @val float
SET @val = RAND()

SELECT NULLIF(REPLICATE('1', CONVERT(int, @val*2)) , ' ')

Grant EXECUTE/SELECT Permissions For All User Defined Functions To A User

You want to create a new user with read and write access who also has ability to execute all user defined functions but you don't want to make the user a db_owner. The code below will do a GRANT EXECUTE/SELECT for all the user defined functions in the DB If the user defined function is a table-valued function then you need to grant select permissions otherwise you need to grant execute permissions

Right now this code will print the GRANT EXECUTE/SELECT statements, change the PRINT to EXEC if you want it to be done automatically

--Grab all the functions for the current DB
SELECT IDENTITY(INT,1,1) AS ID,
SPECIFIC_NAME,DATA_TYPE
INTO #FunctionList
FROM INFORMATION_SCHEMA.ROUTINES --Only Procs
WHERE OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),'IsMSShipped') =0
AND ROUTINE_TYPE='FUNCTION'
ORDER BY SPECIFIC_NAME

DECLARE
@Loopid INT,
@MaxId INT,
@UserName VARCHAR(50)


--This is the user that will get the execute/select permissions
SELECT @UserName = 'SomeUser'


--Grab start and end values for the loop
SELECT @Loopid = 1,
@MaxId = MAX(ID)
FROM #FunctionList

DECLARE
@SQL VARCHAR(500),
@ProcName VARCHAR(400) ,
@Permission VARCHAR(20),
@DataType VARCHAR(20)


--This is where the loop starts
WHILE @Loopid <= @MaxId BEGIN

--grab the function name and type
SELECT @ProcName = SPECIFIC_NAME, @DataType =DATA_TYPE
FROM #FunctionList
WHERE ID = @Loopid

--Find out if it's a table-valued function
IF @DataType ='TABLE'
SELECT @Permission ='SELECT'
ELSE
SELECT @Permission ='EXECUTE'

--construct the statement
SELECT @SQL = 'GRANT ' + @Permission +' ON [' + @ProcName + '] TO ' + @UserName
PRINT (@SQL) --change PRINT to EXECUTE if you want it to run automatically

--increment counter
SET @Loopid = @Loopid + 1
END

--clean up
DROP TABLE #FunctionList