Here are 2 quick ways to list all tables in your database without an index
The first query is using sysobjects and the second query is using the INFORMATION_SCHEMA.TABLES view
SELECT name AS TableName
FROM sysobjects s
WHERE type = 'U'
AND OBJECTPROPERTY(id , 'TableHasIndex' ) = 0
ORDER BY TableName
SELECT TABLE_NAME AS TableName
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE ='BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME) , 'TableHasIndex' ) = 0
ORDER BY TableName
Wednesday, May 17, 2006
Tuesday, May 16, 2006
SQL Compare Without The Price Tag
You want to know the differences between 2 tables but don’t have the money to pay for SQL Compare by red-gate software and your trial version has expired ;-(
Let me say first of all that SQL Compare is probably the best third party product around for SQL Server (along with SQL LITESPEED by QUEST Software)
In order to see what rows are in table1 and not in table 2 and vice versa you can do 2 left joins, 2 right joins or 1 left and 1 right join. To get the rows that are different you can use CHECKSUM
Let’s get started…
--let's copy over 20 rows to a table named authors2
SELECT TOP 20 * INTO tempdb..authors2
FROM pubs..authors
--update 5 records by appending X to the au_fname
SET ROWCOUNT 5
UPDATE tempdb..authors2
SET au_fname =au_fname +'X'
--Set rowcount back to 0
SET ROWCOUNT 0
--let's insert a row that doesn't exist in pubs
INSERT INTO tempdb..authors2
SELECT '666-66-6666', au_lname, au_fname, phone, address, city, state, zip, contract
FROM tempdb..authors2
WHERE au_id ='172-32-1176'
--*** The BIG SELECT QUERY --***
--Not in Pubs
SELECT 'Does Not Exist On Production',t2.au_id
FROM pubs..authors t1
RIGHT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
WHERE t1.au_id IS NULL
UNION ALL
--Not in Temp
SELECT 'Does Not Exist In Staging',t1.au_id
FROM pubs..authors t1
LEFT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
WHERE t2.au_id IS NULL
UNION ALL
--Data Mismatch
SELECT 'Data Mismatch', t1.au_id
FROM( SELECT BINARY_CHECKSUM(*) AS CheckSum1 ,au_id FROM pubs..authors) t1
JOIN(SELECT BINARY_CHECKSUM(*) AS CheckSum2,au_id FROM tempdb..authors2) t2 ON t1.au_id =t2.au_id
WHERE CheckSum1 <> CheckSum2
--Clean up
DROP TABLE tempdb..authors2
GO
You can run this whole script in 1 shot
Let me finish by saying that this code should just be used for quick checking. SQL Compare is far superior to this and will also generate the scripts for you to synchronize the 2 tables. So if you only need to do something like this once then use this code or download the SQL compare trial version. If you do this on a regular basis then you should seriously consider getting SQL compare
I am not being paid or endorsed in any way by red-gate, I just happen to like their product a lot and it saved me a lot of time and trouble once a developer went on vacation
[Edit]
SQL server MVP Louis Davidson has posted a comment and this was his approach
Basically a FULL OUTER JOIN instead of a UNION
So here is his query
SELECT CASE WHEN t1.au_id IS NULL
AND t2.au_id IS NOT NULL
THEN 'Does Not Exist On Production'
WHEN t1.au_id IS NOT NULL
AND t2.au_id IS NULL
THEN 'Does Not Exist In Staging'
ELSE 'Data Mismatch' END,
COALESCE(t1.au_id, t2.au_id) AS au_id
FROM (SELECT *, BINARY_CHECKSUM(*) AS bc FROM pubs..authors) AS t1
FULL OUTER JOIN (SELECT *, BINARY_CHECKSUM(*) AS bc FROM tempdb..authors2) AS t2
ON t1.au_id =t2.au_id
WHERE t1.au_id IS NULL
OR t2.au_id IS NULL
OR t1.bc <> t2.bc
Louis is also the author of the just released Pro SQL Server 2005 Database Design and Optimization book
[/edit]
Let me say first of all that SQL Compare is probably the best third party product around for SQL Server (along with SQL LITESPEED by QUEST Software)
In order to see what rows are in table1 and not in table 2 and vice versa you can do 2 left joins, 2 right joins or 1 left and 1 right join. To get the rows that are different you can use CHECKSUM
Let’s get started…
--let's copy over 20 rows to a table named authors2
SELECT TOP 20 * INTO tempdb..authors2
FROM pubs..authors
--update 5 records by appending X to the au_fname
SET ROWCOUNT 5
UPDATE tempdb..authors2
SET au_fname =au_fname +'X'
--Set rowcount back to 0
SET ROWCOUNT 0
--let's insert a row that doesn't exist in pubs
INSERT INTO tempdb..authors2
SELECT '666-66-6666', au_lname, au_fname, phone, address, city, state, zip, contract
FROM tempdb..authors2
WHERE au_id ='172-32-1176'
--*** The BIG SELECT QUERY --***
--Not in Pubs
SELECT 'Does Not Exist On Production',t2.au_id
FROM pubs..authors t1
RIGHT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
WHERE t1.au_id IS NULL
UNION ALL
--Not in Temp
SELECT 'Does Not Exist In Staging',t1.au_id
FROM pubs..authors t1
LEFT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
WHERE t2.au_id IS NULL
UNION ALL
--Data Mismatch
SELECT 'Data Mismatch', t1.au_id
FROM( SELECT BINARY_CHECKSUM(*) AS CheckSum1 ,au_id FROM pubs..authors) t1
JOIN(SELECT BINARY_CHECKSUM(*) AS CheckSum2,au_id FROM tempdb..authors2) t2 ON t1.au_id =t2.au_id
WHERE CheckSum1 <> CheckSum2
--Clean up
DROP TABLE tempdb..authors2
GO
You can run this whole script in 1 shot
Let me finish by saying that this code should just be used for quick checking. SQL Compare is far superior to this and will also generate the scripts for you to synchronize the 2 tables. So if you only need to do something like this once then use this code or download the SQL compare trial version. If you do this on a regular basis then you should seriously consider getting SQL compare
I am not being paid or endorsed in any way by red-gate, I just happen to like their product a lot and it saved me a lot of time and trouble once a developer went on vacation
[Edit]
SQL server MVP Louis Davidson has posted a comment and this was his approach
Basically a FULL OUTER JOIN instead of a UNION
So here is his query
SELECT CASE WHEN t1.au_id IS NULL
AND t2.au_id IS NOT NULL
THEN 'Does Not Exist On Production'
WHEN t1.au_id IS NOT NULL
AND t2.au_id IS NULL
THEN 'Does Not Exist In Staging'
ELSE 'Data Mismatch' END,
COALESCE(t1.au_id, t2.au_id) AS au_id
FROM (SELECT *, BINARY_CHECKSUM(*) AS bc FROM pubs..authors) AS t1
FULL OUTER JOIN (SELECT *, BINARY_CHECKSUM(*) AS bc FROM tempdb..authors2) AS t2
ON t1.au_id =t2.au_id
WHERE t1.au_id IS NULL
OR t2.au_id IS NULL
OR t1.bc <> t2.bc
Louis is also the author of the just released Pro SQL Server 2005 Database Design and Optimization book
[/edit]
Monday, May 15, 2006
Convert Millisecond To "hh:mm:ss" Format
This question was posted in the Transact-SQL MSDN Forum
You have a column in your table that stores duration in milliseconds
You would like to output this in the following format ‘hh:mm:ss’
So for example 11354013 would be 03:09:14:013 and 86399998 milliseconds would be 23:59:59:997 But be careful because 86399998 will return 23:59:59:997 but 86399999 will return 00:00:00:000 (it will round up to 1 day, 0 hours etc etc)
So let’s see how to do that. Basically all you have to do is add the milliseconds to a zero date and then convert that to varchar with a style of 114 (hh:mi:ss:mmm(24h))
DECLARE @SomeMilliSecondsNumber INT
SELECT @SomeMilliSecondsNumber =11354013
SELECT CONVERT(VARCHAR,DATEADD(ms,@SomeMilliSecondsNumber,0),114)
--03:09:14:013
GO
DECLARE @SomeMilliSecondsNumber INT
SELECT @SomeMilliSecondsNumber =86399998
SELECT CONVERT(VARCHAR,DATEADD(ms,@SomeMilliSecondsNumber,0),114)
--23:59:59:997
GO
DECLARE @SomeMilliSecondsNumber INT
SELECT @SomeMilliSecondsNumber =86399999
SELECT CONVERT(VARCHAR,DATEADD(ms,@SomeMilliSecondsNumber,0),114)
--00:00:00:000
GO
You have a column in your table that stores duration in milliseconds
You would like to output this in the following format ‘hh:mm:ss’
So for example 11354013 would be 03:09:14:013 and 86399998 milliseconds would be 23:59:59:997 But be careful because 86399998 will return 23:59:59:997 but 86399999 will return 00:00:00:000 (it will round up to 1 day, 0 hours etc etc)
So let’s see how to do that. Basically all you have to do is add the milliseconds to a zero date and then convert that to varchar with a style of 114 (hh:mi:ss:mmm(24h))
DECLARE @SomeMilliSecondsNumber INT
SELECT @SomeMilliSecondsNumber =11354013
SELECT CONVERT(VARCHAR,DATEADD(ms,@SomeMilliSecondsNumber,0),114)
--03:09:14:013
GO
DECLARE @SomeMilliSecondsNumber INT
SELECT @SomeMilliSecondsNumber =86399998
SELECT CONVERT(VARCHAR,DATEADD(ms,@SomeMilliSecondsNumber,0),114)
--23:59:59:997
GO
DECLARE @SomeMilliSecondsNumber INT
SELECT @SomeMilliSecondsNumber =86399999
SELECT CONVERT(VARCHAR,DATEADD(ms,@SomeMilliSecondsNumber,0),114)
--00:00:00:000
GO
Friday, May 12, 2006
Syntax Error Converting The Varchar Value To A Column Of Data Type Int
There was a question today on the microsoft.public.sqlserver.programming forum where a person had a problem with an in predicate
The error that was raised was 'Syntax error converting the varchar value '33333A' to a column of data type int'
The error is a little bit misleading
Let's say you have the following query
SELECT * FROM SomeTable WHERE SomeColumn in (12345,22222)
And you get that error, you might think I am not converting anything why am I getting this error?
If the query was the one below then it would make sense
SELECT CONVERT(INT,SomeColumn) FROM SomeTable...
Now let's see what causes this error
--Create the test table and insert 3 rows of data
CREATE TABLE SomeTable (id INT,SomeColumn VARCHAR(49))
INSERT INTO SomeTable VALUES(1,'12345')
INSERT INTO SomeTable VALUES(1,'22222')
INSERT INTO SomeTable VALUES(1,'33333')
--let's select from that table
--no problem
SELECT * FROM SomeTable WHERE SomeColumn in (12345,22222)
--Let's insert something with a non numeric value
INSERT INTO SomeTable VALUES(1,'33333A')
--Let's try again
SELECT * FROM SomeTable WHERE SomeColumn in (12345,22222)
--Oops error
--Server: Msg 245, Level 16, State 1, Line 1
--Syntax error converting the varchar value '33333A' to a column of data type int.
--The trick is to put quotes around the values
--And now it works
SELECT * FROM SomeTable WHERE SomeColumn in ('12345','22222')
--Clean up
DROP TABLE SomeTable
The error that was raised was 'Syntax error converting the varchar value '33333A' to a column of data type int'
The error is a little bit misleading
Let's say you have the following query
SELECT * FROM SomeTable WHERE SomeColumn in (12345,22222)
And you get that error, you might think I am not converting anything why am I getting this error?
If the query was the one below then it would make sense
SELECT CONVERT(INT,SomeColumn) FROM SomeTable...
Now let's see what causes this error
--Create the test table and insert 3 rows of data
CREATE TABLE SomeTable (id INT,SomeColumn VARCHAR(49))
INSERT INTO SomeTable VALUES(1,'12345')
INSERT INTO SomeTable VALUES(1,'22222')
INSERT INTO SomeTable VALUES(1,'33333')
--let's select from that table
--no problem
SELECT * FROM SomeTable WHERE SomeColumn in (12345,22222)
--Let's insert something with a non numeric value
INSERT INTO SomeTable VALUES(1,'33333A')
--Let's try again
SELECT * FROM SomeTable WHERE SomeColumn in (12345,22222)
--Oops error
--Server: Msg 245, Level 16, State 1, Line 1
--Syntax error converting the varchar value '33333A' to a column of data type int.
--The trick is to put quotes around the values
--And now it works
SELECT * FROM SomeTable WHERE SomeColumn in ('12345','22222')
--Clean up
DROP TABLE SomeTable
Changing The Schema Of A Table In SQL Server 2005
Since I am still in unpacking and putting up light-fixtures mode I decided to link to a cool post on the SQL Doctor's blog
It covers the process of changing the schema for a table
All you need to do is this
ALTER SCHEMA [schemaName] TRANSFER [otherSchemaName].[objectName]
Here is the link (http://spaces.msn.com/drsql/blog/cns!80677FB08B3162E4!1041.entry)
And don't let Fred, Barney, Wilma or Betty fool you
It covers the process of changing the schema for a table
All you need to do is this
ALTER SCHEMA [schemaName] TRANSFER [otherSchemaName].[objectName]
Here is the link (http://spaces.msn.com/drsql/blog/cns!80677FB08B3162E4!1041.entry)
And don't let Fred, Barney, Wilma or Betty fool you
Thursday, May 11, 2006
String Or Binary Data Would Be Truncated
I have seen a couple of Google searches hitting this blog with the search 'String or binary data would be truncated'
Basically what the error message 'String or binary data would be truncated' means is that the data that you are trying to fit into a column inside a table won't fit because the column isn't wide enough
A lot of times this occurs when you are inserting data into 1 table from another table
For example you have a table on some database server and you will need to import that data into a new table
You will have to store Unicode in the future so you make one of the columns nvarchar, you don't bother to check for the maximum length of the data in the original table and just make you column nvarchar(4000) (the max in SQL server 2000)
Now let's test a couple of these cases
--Create the 'original' table
CREATE TABLE TestData (ID int,
SomeValue VARCHAR(5000),
SomeOtherValue VARCHAR(6))
--add some data
INSERT INTO TestData VALUES (1,REPLICATE('A',4002),'abcdef')
INSERT INTO TestData VALUES (2,'123','abcde')
INSERT INTO TestData VALUES (3,'123','abcdef')
--Create the 'new' table
CREATE TABLE TestDataTruncate2 (ID INT,
SomeValue NVARCHAR(4000),
SomeOtherValue VARCHAR(5)) --Oops 1 less than in the original table
--Fails
INSERT INTO TestDataTruncate
SELECT * FROM TestData
WHERE ID =1
/*
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Fails because nvarchar can only hold 4000 characters
*/
--No Problem
INSERT INTO TestDataTruncate
SELECT * FROM TestData
WHERE ID =2
--Fails
INSERT INTO TestDataTruncate
SELECT * FROM TestData
WHERE ID =3
/*
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Fails because SomeOtherValue has 6 charaters of data
but the column is only 5 characters in the new table
*/
--Instead of opening up 2 windows and comparing the tables column by column
--you can use the query below to return all that info in a resultset
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='TestDataTruncate'
UNION ALL
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='TestData'
ORDER BY ORDINAL_POSITION,TABLE_NAME
--you can also create a self join and return only the columns
--where the name is the same but the column size is different
SELECT t1.COLUMN_NAME,t1.DATA_TYPE,
t1.CHARACTER_MAXIMUM_LENGTH,t2.CHARACTER_MAXIMUM_LENGTH,t1.ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS t1
JOIN INFORMATION_SCHEMA.COLUMNS t2 ON t1.COLUMN_NAME =t2.COLUMN_NAME AND t1.TABLE_NAME <> t2.TABLE_NAME
WHERE t1.TABLE_NAME ='TestDataTruncate'
AND t2.TABLE_NAME ='TestData'
AND t1.CHARACTER_MAXIMUM_LENGTH <> t2.CHARACTER_MAXIMUM_LENGTH
--Strangely enough when you try to assign values to a variable
--it will truncate it without an error message
DECLARE @chvCity VARCHAR(8)
SELECT @chvCity ='Princeton'
SELECT @chvCity
--Clean up this mess ;-)
DROP TABLE TestDataTruncate,TestData
Basically what the error message 'String or binary data would be truncated' means is that the data that you are trying to fit into a column inside a table won't fit because the column isn't wide enough
A lot of times this occurs when you are inserting data into 1 table from another table
For example you have a table on some database server and you will need to import that data into a new table
You will have to store Unicode in the future so you make one of the columns nvarchar, you don't bother to check for the maximum length of the data in the original table and just make you column nvarchar(4000) (the max in SQL server 2000)
Now let's test a couple of these cases
--Create the 'original' table
CREATE TABLE TestData (ID int,
SomeValue VARCHAR(5000),
SomeOtherValue VARCHAR(6))
--add some data
INSERT INTO TestData VALUES (1,REPLICATE('A',4002),'abcdef')
INSERT INTO TestData VALUES (2,'123','abcde')
INSERT INTO TestData VALUES (3,'123','abcdef')
--Create the 'new' table
CREATE TABLE TestDataTruncate2 (ID INT,
SomeValue NVARCHAR(4000),
SomeOtherValue VARCHAR(5)) --Oops 1 less than in the original table
--Fails
INSERT INTO TestDataTruncate
SELECT * FROM TestData
WHERE ID =1
/*
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Fails because nvarchar can only hold 4000 characters
*/
--No Problem
INSERT INTO TestDataTruncate
SELECT * FROM TestData
WHERE ID =2
--Fails
INSERT INTO TestDataTruncate
SELECT * FROM TestData
WHERE ID =3
/*
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Fails because SomeOtherValue has 6 charaters of data
but the column is only 5 characters in the new table
*/
--Instead of opening up 2 windows and comparing the tables column by column
--you can use the query below to return all that info in a resultset
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='TestDataTruncate'
UNION ALL
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='TestData'
ORDER BY ORDINAL_POSITION,TABLE_NAME
--you can also create a self join and return only the columns
--where the name is the same but the column size is different
SELECT t1.COLUMN_NAME,t1.DATA_TYPE,
t1.CHARACTER_MAXIMUM_LENGTH,t2.CHARACTER_MAXIMUM_LENGTH,t1.ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS t1
JOIN INFORMATION_SCHEMA.COLUMNS t2 ON t1.COLUMN_NAME =t2.COLUMN_NAME AND t1.TABLE_NAME <> t2.TABLE_NAME
WHERE t1.TABLE_NAME ='TestDataTruncate'
AND t2.TABLE_NAME ='TestData'
AND t1.CHARACTER_MAXIMUM_LENGTH <> t2.CHARACTER_MAXIMUM_LENGTH
--Strangely enough when you try to assign values to a variable
--it will truncate it without an error message
DECLARE @chvCity VARCHAR(8)
SELECT @chvCity ='Princeton'
SELECT @chvCity
--Clean up this mess ;-)
DROP TABLE TestDataTruncate,TestData
Tuesday, May 09, 2006
Moved Into A New House
I have moved into a new house over the last week. If you are thinking of selling your house and buying a new one at the same time then either wait until you hit the jackpot/win the lotto or wait until your old house is paid off. This has been the most stressful experience of my life. The buyer of my house was giving me a hard time, then she couldn’t get a mortgage commitment and after that she decided to switch mortgage companies. Nothing was certain until the last millisecond. And the fact that my wife is 7 months pregnant with twins didn’t help either with this ordeal. I won’t have an internet connection until this weekend so I probably won’t post a lot this week
This post and the one that I will do after the twins are born are probably the only two non SQL related posts that will ever be on this blog, hopefully you don’t mind
This post and the one that I will do after the twins are born are probably the only two non SQL related posts that will ever be on this blog, hopefully you don’t mind
Tuesday, May 02, 2006
Top SQL Server Google Searches For April 2006
These are the top SQL Searches on this site for the month of April. I have left out searches that have nothing to do with SQL Server or programming. Here are the results...
sql server CONNECTIVITY ERRORS thro dsl after installing xp sp2
stored procedure divide
execution plans
String or binary data would be truncated
rank() performance
copy table to other server
rank() performance sql
SQL SERVER 2005 SELECT FIELD AS VARIABLE
ToBase64String sql procedure
Server: Msg 7357, Level 16, State 2, Line 1
"= isNull"
It's always interesting to see what people are searching for, I left the money laundry one in this list because I thought it was kind of amusing
I always like to look at this list so that I know what people are interested in and I can write a little thing about it
sql server CONNECTIVITY ERRORS thro dsl after installing xp sp2
stored procedure divide
execution plans
String or binary data would be truncated
rank() performance
copy table to other server
rank() performance sql
SQL SERVER 2005 SELECT FIELD AS VARIABLE
ToBase64String sql procedure
Server: Msg 7357, Level 16, State 2, Line 1
"= isNull"
It's always interesting to see what people are searching for, I left the money laundry one in this list because I thought it was kind of amusing
I always like to look at this list so that I know what people are interested in and I can write a little thing about it
Top 10 Articles Of All Time
Below are the top 10 articles of all time according to Google Analytics (updated 6/1/2006)
There is a link to it right below the Google Search box, I will update this once a month
1 SQL Query Optimizations
2 Five Ways To Return Values From Stored Procedures
3 Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
4 COALESCE And ISNULL Differences
5 SQL Server 2005 Free E-Learning
6 OPENROWSET And Excel Problems
7 Do You Know How Between Works With Dates?
8 NULL Trouble In SQL Server Land
9 Find all Primary and Foreign Keys In A Database
10 Fun With SQL Server Update Triggers
There is a link to it right below the Google Search box, I will update this once a month
1 SQL Query Optimizations
2 Five Ways To Return Values From Stored Procedures
3 Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
4 COALESCE And ISNULL Differences
5 SQL Server 2005 Free E-Learning
6 OPENROWSET And Excel Problems
7 Do You Know How Between Works With Dates?
8 NULL Trouble In SQL Server Land
9 Find all Primary and Foreign Keys In A Database
10 Fun With SQL Server Update Triggers
Top 5 SQL Server Posts For April 2006
Below are the top 5 posts according to Google Analytics for the month of April
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
OPENROWSET And Excel Problems
SQL Query Optimizations
SQL Server 2005 Free E-Learning
Feature Pack for Microsoft SQL Server 2005
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
OPENROWSET And Excel Problems
SQL Query Optimizations
SQL Server 2005 Free E-Learning
Feature Pack for Microsoft SQL Server 2005
A Developer’s Guide to SQL Server 2005
The follow up to the highly succesful book 'A First Look at SQL Server 2005 for Developers' has been published by Addison-Wesley
The book is 1008 pages long
From the site:
Few technologies have been as eagerly anticipated as Microsoft SQL Server 2005. Now, two SQL Server insiders deliver the definitive hands-on guide--accurate, comprehensive, and packed with examples. A Developer's Guide to SQL Server 2005 starts where Microsoft's documentation, white papers, and Web articles leave off, showing developers how to take full advantage of SQL Server 2005's key innovations. It draws on exceptional cooperation from Microsoft's SQL Server developers and the authors' extensive access to SQL Server 2005 since its earliest alpha releases.
You'll find practical explanations of the new SQL Server 2005 data model, built-in .NET hosting, improved programmability, SQL:1999 compliance, and much more. Virtually every key concept is illuminated via sample code that has been fully updated for and tested with the shipping version of the product.
Key coverage includes
Using SQL Server 2005 as a .NET runtime host: extending the server while enhancing security, reliability, and performance
Writing procedures, functions, triggers, and types in .NET languages
Exploiting enhancements to T-SQL for robust error-handling, efficient queries, and improved syntax
Effectively using the XML data type and XML queries
Implementing native SQL Server 2005 Web Services
Writing efficient, robust clients for SQL Server 2005 using ADO.NET, classic ADO, and other APIs
Taking full advantage of user-defined types (UDTs), query notifications, promotable transactions, and multiple active result sets (MARS)
Using SQL Management Objects (SMO), SQL Service Broker, and SQL Server Notification Services to build integrated applications
Download the Sample Chapter related to this title.
The Amazon link is here
Monday, May 01, 2006
Check For Not In Table Values (16 Different Ways And Counting)
How many times did you see a question like this:
how do I select all rows from a table where the column value is not 'ValueA' and not 'ValueB'
Well I decided to figure out how many different ways there are to do such a query
I came up with 16 different ways so far, some of them will cause a table scan, some of them will cause a index seek instead
You can test them out for yourself to see which ones perform the best
CREATE TABLE #TestTable (
Objects VARCHAR(20) PRIMARY KEY)
INSERT INTO #TestTable
VALUES ('boat')
INSERT INTO #TestTable
VALUES('ship')
INSERT INTO #TestTable
VALUES('car')
INSERT INTO #TestTable
VALUES('bus')
INSERT INTO #TestTable
VALUES ('airplane')
-- #1 <>
SELECT *,1
FROM #TestTable
WHERE Objects <> 'boat'
AND Objects <> 'ship'
-- #2 !=
SELECT *,2
FROM #TestTable
WHERE Objects != 'boat'
AND Objects != 'ship'
-- #3 NOT
SELECT *,3
FROM #TestTable
WHERE NOT (Objects = 'boat'
OR Objects = 'ship')
-- #4 NOT IN
SELECT *,4
FROM #TestTable
WHERE Objects NOT IN ('boat',
'ship')
-- #5 ALL
SELECT *,5
FROM #TestTable
WHERE Objects <> ALL (SELECT 'boat'
UNION ALL
SELECT 'ship')
-- #6 ANY
SELECT *,6
FROM #TestTable
WHERE NOT Objects = ANY (SELECT 'boat'
UNION ALL
SELECT 'ship')
-- #7 SOME
SELECT *,7
FROM #TestTable
WHERE NOT Objects = SOME (SELECT 'boat'
UNION ALL
SELECT 'ship')
-- #8 NOT IN with subquery
SELECT *,8
FROM #TestTable
WHERE Objects NOT IN (SELECT 'boat'
UNION ALL
SELECT 'ship')
-- #9 NOT EXISTS
SELECT *,9
FROM #TestTable T
WHERE NOT EXISTS (SELECT *
FROM (SELECT 'boat' AS Objects
UNION ALL
SELECT 'ship') F
WHERE F.Objects = T.Objects)
-- #10 LEFT OUTER JOIN
SELECT T.*,10
FROM #TestTable T
LEFT OUTER JOIN (SELECT 'boat' AS Objects
UNION ALL
SELECT 'ship') F
ON T.Objects = F.Objects
WHERE F.Objects IS NULL
-- #11 CHARINDEX
SELECT *,11
FROM #TestTable
WHERE CHARINDEX('-' + Objects + '-','-boat-ship-') = 0
-- #12 RIGHT OUTER JOIN
SELECT T.*,12
FROM (SELECT 'boat' AS Objects
UNION ALL
SELECT 'ship') F
RIGHT OUTER JOIN #TestTable T
ON T.Objects = F.Objects
WHERE F.Objects IS NULL
AND T.Objects IS NOT NULL
-- #13 FULL OUTER JOIN
SELECT T.*,13
FROM (SELECT 'boat' AS Objects
UNION ALL
SELECT 'ship') F
FULL OUTER JOIN #TestTable T
ON T.Objects = F.Objects
WHERE F.Objects IS NULL
-- #14 PATINDEX
SELECT *,14
FROM #TestTable
WHERE PATINDEX('%-%' + Objects + '%-%','-boat-ship-') = 0
-- #15 PARSENAME
SELECT *,15
FROM #TestTable
WHERE Objects <> PARSENAME('boat.ship',1)
AND Objects <> PARSENAME('boat.ship',2)
-- #16 REVERSE and NOT IN
SELECT *,16
FROM #TestTable
WHERE REVERSE(Objects) NOT IN ('taob','pihs')
-- And here is another one that BugsBunny suggested in a comment
-- #17 NOT LIKE
SELECT *,17
FROM #TestTable
WHERE ',' + 'boat,ship' + ',' NOT LIKE '%,' + Objects + ',%'
DROP TABLE #TestTable
So that's it, if you know of another way, leave me a comment or email me and I will add it to this list
how do I select all rows from a table where the column value is not 'ValueA' and not 'ValueB'
Well I decided to figure out how many different ways there are to do such a query
I came up with 16 different ways so far, some of them will cause a table scan, some of them will cause a index seek instead
You can test them out for yourself to see which ones perform the best
CREATE TABLE #TestTable (
Objects VARCHAR(20) PRIMARY KEY)
INSERT INTO #TestTable
VALUES ('boat')
INSERT INTO #TestTable
VALUES('ship')
INSERT INTO #TestTable
VALUES('car')
INSERT INTO #TestTable
VALUES('bus')
INSERT INTO #TestTable
VALUES ('airplane')
-- #1 <>
SELECT *,1
FROM #TestTable
WHERE Objects <> 'boat'
AND Objects <> 'ship'
-- #2 !=
SELECT *,2
FROM #TestTable
WHERE Objects != 'boat'
AND Objects != 'ship'
-- #3 NOT
SELECT *,3
FROM #TestTable
WHERE NOT (Objects = 'boat'
OR Objects = 'ship')
-- #4 NOT IN
SELECT *,4
FROM #TestTable
WHERE Objects NOT IN ('boat',
'ship')
-- #5 ALL
SELECT *,5
FROM #TestTable
WHERE Objects <> ALL (SELECT 'boat'
UNION ALL
SELECT 'ship')
-- #6 ANY
SELECT *,6
FROM #TestTable
WHERE NOT Objects = ANY (SELECT 'boat'
UNION ALL
SELECT 'ship')
-- #7 SOME
SELECT *,7
FROM #TestTable
WHERE NOT Objects = SOME (SELECT 'boat'
UNION ALL
SELECT 'ship')
-- #8 NOT IN with subquery
SELECT *,8
FROM #TestTable
WHERE Objects NOT IN (SELECT 'boat'
UNION ALL
SELECT 'ship')
-- #9 NOT EXISTS
SELECT *,9
FROM #TestTable T
WHERE NOT EXISTS (SELECT *
FROM (SELECT 'boat' AS Objects
UNION ALL
SELECT 'ship') F
WHERE F.Objects = T.Objects)
-- #10 LEFT OUTER JOIN
SELECT T.*,10
FROM #TestTable T
LEFT OUTER JOIN (SELECT 'boat' AS Objects
UNION ALL
SELECT 'ship') F
ON T.Objects = F.Objects
WHERE F.Objects IS NULL
-- #11 CHARINDEX
SELECT *,11
FROM #TestTable
WHERE CHARINDEX('-' + Objects + '-','-boat-ship-') = 0
-- #12 RIGHT OUTER JOIN
SELECT T.*,12
FROM (SELECT 'boat' AS Objects
UNION ALL
SELECT 'ship') F
RIGHT OUTER JOIN #TestTable T
ON T.Objects = F.Objects
WHERE F.Objects IS NULL
AND T.Objects IS NOT NULL
-- #13 FULL OUTER JOIN
SELECT T.*,13
FROM (SELECT 'boat' AS Objects
UNION ALL
SELECT 'ship') F
FULL OUTER JOIN #TestTable T
ON T.Objects = F.Objects
WHERE F.Objects IS NULL
-- #14 PATINDEX
SELECT *,14
FROM #TestTable
WHERE PATINDEX('%-%' + Objects + '%-%','-boat-ship-') = 0
-- #15 PARSENAME
SELECT *,15
FROM #TestTable
WHERE Objects <> PARSENAME('boat.ship',1)
AND Objects <> PARSENAME('boat.ship',2)
-- #16 REVERSE and NOT IN
SELECT *,16
FROM #TestTable
WHERE REVERSE(Objects) NOT IN ('taob','pihs')
-- And here is another one that BugsBunny suggested in a comment
-- #17 NOT LIKE
SELECT *,17
FROM #TestTable
WHERE ',' + 'boat,ship' + ',' NOT LIKE '%,' + Objects + ',%'
DROP TABLE #TestTable
So that's it, if you know of another way, leave me a comment or email me and I will add it to this list
Friday, April 28, 2006
Splitting City, State and Zipcode From One Column (Or Flat file)
Sometimes you deal with vendors, customers or agencies and you have to do a file exchange
Of course the format of these files is always dictated by these people so what do you do when a CityStateZip column contains values like “Long Island City, NY 10013” or like “Princeton,NJ 08536 “? But in your Database it is normalized of course, and you have 3 columns. You will have to use a combination of LEFT, LTRIM, SUBSTRING, REPLACE and RIGHT. I am taking into account that there could be spaces in the column or even spaces in the name (New York City) So let’s get started and see what we get
CREATE TABLE #TestCityStateZip (csz CHAR(49))
INSERT INTO #TestCityStateZip VALUES ('city ,st 12223')
INSERT INTO #TestCityStateZip VALUES ('New York City,NY 10028')
INSERT INTO #TestCityStateZip VALUES ('Princeton , NJ 08536')
INSERT INTO #TestCityStateZip VALUES ('Princeton,NJ 08536 ')
INSERT INTO #TestCityStateZip VALUES ('Long Island City, NY 10013')
INSERT INTO #TestCityStateZip VALUES ('Long Island City, NY 10013 ')
INSERT INTO #TestCityStateZip VALUES ('Long Island City , NY 10013')
INSERT INTO #TestCityStateZip VALUES ('Long Island City ,NY 10013 ')
SELECT LEFT(csz,CHARINDEX(',',csz)-1)AS City,
LEFT(LTRIM(SUBSTRING(csz,(CHARINDEX(',',csz)+1),4)),2) AS State,
RIGHT(RTRIM(csz),CHARINDEX(' ',REVERSE(RTRIM(csz)))-1) AS Zip
FROM #TestCityStateZip
GO
Of course the format of these files is always dictated by these people so what do you do when a CityStateZip column contains values like “Long Island City, NY 10013” or like “Princeton,NJ 08536 “? But in your Database it is normalized of course, and you have 3 columns. You will have to use a combination of LEFT, LTRIM, SUBSTRING, REPLACE and RIGHT. I am taking into account that there could be spaces in the column or even spaces in the name (New York City) So let’s get started and see what we get
CREATE TABLE #TestCityStateZip (csz CHAR(49))
INSERT INTO #TestCityStateZip VALUES ('city ,st 12223')
INSERT INTO #TestCityStateZip VALUES ('New York City,NY 10028')
INSERT INTO #TestCityStateZip VALUES ('Princeton , NJ 08536')
INSERT INTO #TestCityStateZip VALUES ('Princeton,NJ 08536 ')
INSERT INTO #TestCityStateZip VALUES ('Long Island City, NY 10013')
INSERT INTO #TestCityStateZip VALUES ('Long Island City, NY 10013 ')
INSERT INTO #TestCityStateZip VALUES ('Long Island City , NY 10013')
INSERT INTO #TestCityStateZip VALUES ('Long Island City ,NY 10013 ')
SELECT LEFT(csz,CHARINDEX(',',csz)-1)AS City,
LEFT(LTRIM(SUBSTRING(csz,(CHARINDEX(',',csz)+1),4)),2) AS State,
RIGHT(RTRIM(csz),CHARINDEX(' ',REVERSE(RTRIM(csz)))-1) AS Zip
FROM #TestCityStateZip
GO
Tuesday, April 25, 2006
SQL Server 2005 SP1 Books Online (April 2006) For Download
SQL Server 2005 SP1 Books Online (April 2006) is available for download
Download an updated version of Books Online for Microsoft SQL Server 2005. Books Online is the primary documentation for SQL Server 2005. The April 2006 update to Books Online contains new material and fixes to documentation problems reported by customers after SQL Server 2005 was released. Refer to "New and Updated Books Online Topics" for a list of topics that are new or updated in this version. Topics with significant updates have a Change History table at the bottom of the topic that summarizes the changes. Beginning with the April 2006 update, SQL Server 2005 Books Online reflects product upgrades included in SQL Server 2005 Service Pack 1 (SP1).
The download is 121 MB
http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&DisplayLang=en
Download an updated version of Books Online for Microsoft SQL Server 2005. Books Online is the primary documentation for SQL Server 2005. The April 2006 update to Books Online contains new material and fixes to documentation problems reported by customers after SQL Server 2005 was released. Refer to "New and Updated Books Online Topics" for a list of topics that are new or updated in this version. Topics with significant updates have a Change History table at the bottom of the topic that summarizes the changes. Beginning with the April 2006 update, SQL Server 2005 Books Online reflects product upgrades included in SQL Server 2005 Service Pack 1 (SP1).
The download is 121 MB
http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&DisplayLang=en
Monday, April 24, 2006
How To Script Multiple Jobs Or Stored Procedures In SQL Server 2005 Management Studio
One of the biggest complaints of people who moved from SQL server 2000 to SQL Server 2005 is the inability to script multiple objects. Well that feature is still available only it’s kind of hidden
Click the Jobs or Stored Procedures folder in SQL Server Management Studio, and then hit the F7 key; this will bring up the Summary pane. Highlight all the Jobs or Stored Procedures that you want to script using a combination of Shift and Ctrl keys, then right click, Script Job/Stored Procedure as..., and then choose where to save this script to. An image of how to script Stored Procedures is below
Click the Jobs or Stored Procedures folder in SQL Server Management Studio, and then hit the F7 key; this will bring up the Summary pane. Highlight all the Jobs or Stored Procedures that you want to script using a combination of Shift and Ctrl keys, then right click, Script Job/Stored Procedure as..., and then choose where to save this script to. An image of how to script Stored Procedures is below
Thursday, April 20, 2006
SQL Server 2005 Service Pack 1 Download Link
Yesterday I reported that Service Pack 1 was available for download but I never posted the link so here it is with a 24 hour delay
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=cb6c71ea-d649-47ff-9176-e7cac58fd4bc
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=cb6c71ea-d649-47ff-9176-e7cac58fd4bc
Wednesday, April 19, 2006
Microsoft Releases SQL Server 2005 Service Pack 1
Microsoft Corp. today announced the availability of Microsoft® SQL Server™ 2005 Service Pack 1 (SP1), the product’s first major milestone since the launch of SQL Server 2005 only a few months ago. The release follows the remarkable reception, momentum and feedback offered by customers and partners in 92 countries. This has informed and accelerated Microsoft’s multiyear vision of Your Data, Any Place, Any Time. Microsoft SQL Server 2005 SP1 includes production-ready Database Mirroring functionality and the new SQL Server Management Studio Express, along with additional updates to SQL Server 2005 Express Edition targeted at users such as independent software vendors (ISVs) wanting to take advantage of greater functionality.
SP1 delivers production-ready Database Mirroring functionality for continuous availability. This complements the existing Always On Technologies in SQL Server 2005 such as failover clustering, database snapshots, snapshot isolation and log shipping. Since launch, Database Mirroring has been extensively tested by Microsoft and its thriving customer community to help ensure that it provides the high availability necessary for the largest customer deployments. Today more than 20 SQL Server customers have deployed Database Mirroring into production
SP1 extends business insight to smaller-scale customers and ISVs using the free SQL Server 2005 Express Edition, which includes SQL Server Reporting Services (SSRS) functionality, Full Text Search and the newly released SQL Server Management Studio Express. This optional set of capabilities is already receiving positive feedback from Microsoft’s ISV partners
SP1 also advances dynamic applications with the new SQL Server Management Studio Express, a simplified graphical management environment for SQL Server Express Edition. This tool builds on Microsoft’s commitment to extending the breadth of the SQL Server family to support all the data storage needs of Microsoft’s customers. It also complements the recently announced SQL Server Everywhere Edition, a lightweight and rich subset of capabilities found in other SQL Server editions, targeted for application embedded storage on clients. SQL Server Everywhere Edition is targeted to be available as a Community Technology Preview (CTP) this summer and released by year end.
For end-to-end business insight, SP1 extends SSRS to support enterprise reporting on SAP NetWeaver Business Intelligence with two new components in SP1: a Microsoft .NET Data provider for SAP NetWeaver Business Intelligence and a new MDX Query Designer. This new functionality extends the benefits of enterprise reporting with SQL Server by enabling SAP customers to easily create and manage reports on information inside any SAP BW data warehouse
SP1 delivers production-ready Database Mirroring functionality for continuous availability. This complements the existing Always On Technologies in SQL Server 2005 such as failover clustering, database snapshots, snapshot isolation and log shipping. Since launch, Database Mirroring has been extensively tested by Microsoft and its thriving customer community to help ensure that it provides the high availability necessary for the largest customer deployments. Today more than 20 SQL Server customers have deployed Database Mirroring into production
SP1 extends business insight to smaller-scale customers and ISVs using the free SQL Server 2005 Express Edition, which includes SQL Server Reporting Services (SSRS) functionality, Full Text Search and the newly released SQL Server Management Studio Express. This optional set of capabilities is already receiving positive feedback from Microsoft’s ISV partners
SP1 also advances dynamic applications with the new SQL Server Management Studio Express, a simplified graphical management environment for SQL Server Express Edition. This tool builds on Microsoft’s commitment to extending the breadth of the SQL Server family to support all the data storage needs of Microsoft’s customers. It also complements the recently announced SQL Server Everywhere Edition, a lightweight and rich subset of capabilities found in other SQL Server editions, targeted for application embedded storage on clients. SQL Server Everywhere Edition is targeted to be available as a Community Technology Preview (CTP) this summer and released by year end.
For end-to-end business insight, SP1 extends SSRS to support enterprise reporting on SAP NetWeaver Business Intelligence with two new components in SP1: a Microsoft .NET Data provider for SAP NetWeaver Business Intelligence and a new MDX Query Designer. This new functionality extends the benefits of enterprise reporting with SQL Server by enabling SAP customers to easily create and manage reports on information inside any SAP BW data warehouse
Inside Microsoft SQL Server 2005: T-SQL Querying
How did I miss this? SQL Server MVP Itzik Ben-Gan has published his latest book: Inside Microsoft SQL Server 2005: T-SQL Querying. For all of you who read SQL Server magazine you probably know Itzik from his great SQL tips and puzzles articles.Take a detailed look at the internal architecture of T-SQL—and unveil the power of set-based querying—with comprehensive reference and advice from the experts. Database developers and administrators get best practices, sample databases, and code to master the intricacies of the programming language—solving complex problems with real-world solutions.
Discover how to:
•Understand logical and physical query processing
•Apply a methodology to optimize query tuning
•Solve relational division problems
•Use CTEs and ranking functions to simplify and optimize solutions
•Aggregate data with various techniques, including tiebreakers, pivoting, histograms, and grouping factors
•Use the TOP option in a query to modify data
•Query specialized data structures with recursive logic, materialized path, or nested sets solutions
•PLUS—Improve your logic and get to the heart of querying problems with logic puzzles
The book is 640 printed pages, below is the table of contents
Chapter 01 - Logical Query Processing
Chapter 02 - Physical Query Processing
Chapter 03 - Query Tuning
Chapter 04 - Subqueries, Table Expressions and Ranking Functions
Chapter 05 - Joins and Set Operations
Chapter 06 - Aggregating and Pivoting Data
Chapter 07 - TOP and APPLY
Chapter 08 - Data Modification
Chapter 09 - Graphs, Trees, Hierarchies and Recursive Queries
Appendix A - Logic Puzzles
I could not find a sample chapter yet but as soon one is available I will create a link to it
If you are intereseted in purchasing this book the Amazon link is here
Tuesday, April 18, 2006
Use PARSENAME, CHARINDEX, PATINDEX or SUBSTRING To Grab Values Up To A Certain Character
This is a question that came up yesterday in the Getting started with SQL Server MSDN forum (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=353250&SiteID=1)
A person wanted to use a MID function in SQL Server, There is no MID function in SQL Server but there are at least 4 ways to implement what the person tried to accomplish
Basically if the data looked like this
aaa-bbbbb
ppppp-bbbbb
zzzz-xxxxx
Then we need to grab everything up until the minus sign
So let's get started
CREATE TABLE #Test (
SomeField VARCHAR(49))
INSERT INTO #Test
VALUES ('aaa-bbbbb')
INSERT INTO #Test
VALUES ('ppppp-bbbbb')
INSERT INTO #Test
VALUES ('zzzz-xxxxx')
--using PARSENAME
SELECT PARSENAME(REPLACE(SomeField,'-','.'),2)
FROM #Test
--using LEFT and CHARINDEX
SELECT LEFT(SomeField,CHARINDEX('-',SomeField) - 1)
FROM #Test
--using LEFT and PATINDEX
SELECT LEFT(SomeField,PATINDEX('%-%',SomeField) - 1)
FROM #Test
--using CASE SUBSTRING and LEFT
SELECT CASE SUBSTRING(SomeField,4,1)
WHEN '-' THEN LEFT(SomeField,3)
ELSE LEFT(SomeField,4)
END
FROM #Test
--clean up
DROP TABLE #Test
A person wanted to use a MID function in SQL Server, There is no MID function in SQL Server but there are at least 4 ways to implement what the person tried to accomplish
Basically if the data looked like this
aaa-bbbbb
ppppp-bbbbb
zzzz-xxxxx
Then we need to grab everything up until the minus sign
So let's get started
CREATE TABLE #Test (
SomeField VARCHAR(49))
INSERT INTO #Test
VALUES ('aaa-bbbbb')
INSERT INTO #Test
VALUES ('ppppp-bbbbb')
INSERT INTO #Test
VALUES ('zzzz-xxxxx')
--using PARSENAME
SELECT PARSENAME(REPLACE(SomeField,'-','.'),2)
FROM #Test
--using LEFT and CHARINDEX
SELECT LEFT(SomeField,CHARINDEX('-',SomeField) - 1)
FROM #Test
--using LEFT and PATINDEX
SELECT LEFT(SomeField,PATINDEX('%-%',SomeField) - 1)
FROM #Test
--using CASE SUBSTRING and LEFT
SELECT CASE SUBSTRING(SomeField,4,1)
WHEN '-' THEN LEFT(SomeField,3)
ELSE LEFT(SomeField,4)
END
FROM #Test
--clean up
DROP TABLE #Test
Monday, April 17, 2006
Grant Execute/SELECT Permissions For All User Defined Functions To A User
You want to add a new user with read and write access and also the 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 prints 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'
Right now this code prints 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
Subscribe to:
Posts (Atom)