How to create a procedure in all databases in a server?

I had a situation where I wanted to create a stored procedure in all databases for a specified SQL server.

I created a below code for that purpose.

SQL Azure Database

SQL Azure Database is Microsoft's cloud-based relational database system which is based on Microsoft SQL Server. Earlier it was called SQL Data Services. SQL Azure CTP 2 is now available for MSDN subscribers.


SQL Azure FAQ

SQL Azure Team Blog

View Jobs Schedule Information

The code portion given below is useful when you need to get the schedule information of SQL Server jobs. There are several system tables exist in msdb database to retrieve jobs related information. But in this scenario I wanted to see the jobs schedule description which displays in jobs properties UI.

The description is not stored in any system tables as is rather it will do a manipulation by using other values. 


It will use the system SP, sp_get_schedule_description   to prepare description. 



create table #temp_jobschedule
(
job_id uniqueidentifier
,schedule_id int
,schedule_name sysname
,[enabled] int
,freq_type int
,freq_interval int
,freq_subday_type int
,freq_subday_interval int
,freq_relative_interval int
,freq_recurrence_factor int
,active_start_date int
,active_end_date int
,active_start_time int
,active_end_time int
,date_created datetime
,schedule_description nvarchar(4000)
,next_run_date int
,next_run_time int
,schedule_uid uniqueidentifier
,job_count int

)




declare @Job_Id uniqueidentifier
declare @Last_Job_Id uniqueidentifier


select top 1 @Job_Id=J.job_id
from msdb..sysjobs j
where j.enabled=1
and j.category_id not in (10,15,13)
order by J.job_id


select top 1 @Last_Job_Id=J.job_id
from msdb..sysjobs j
where j.enabled=1
and j.category_id not in (10,15,13)
order by J.job_id desc


while @Job_Id is not null
begin


insert into #temp_jobschedule (schedule_id,schedule_name,[enabled],freq_type,freq_interval,freq_subday_type,freq_subday_interval,freq_relative_interval,freq_recurrence_factor
,active_start_date,active_end_date,active_start_time,active_end_time,date_created,schedule_description,next_run_date,next_run_time,schedule_uid,job_count)
exec sp_executesql N'exec msdb..sp_help_jobschedule @job_id=@JOB_ID,@include_description=1',N'@JOB_ID uniqueidentifier', @JOB_ID=@Job_Id

select top 1 @Job_Id=J.job_id
from msdb..sysjobs j
where j.[enabled]=1
and j.category_id not in (10,15,13)
and J.job_id>@Job_Id
order by J.job_id

if @Job_Id=@Last_Job_Id
set @Job_Id=null


end


update #temp_jobschedule set job_id=jsch.job_id
from msdb.dbo.sysjobschedules jsch
where #temp_jobschedule.schedule_id=jsch.schedule_id


select j.name,schedule_description,jsrv.last_run_duration
from #temp_jobschedule jsch
inner join msdb.dbo.sysjobs j
on jsch.job_id=j.job_id
inner join msdb.dbo.sysjobservers jsrv
on jsch.job_id=jsrv.job_id

Undocumented DBCC Commands

I found this article while searching about DBCC commands. This is really interesting one specially for the people who wants to know SQL Server internals. This article applies for SQL Server 7 and 2000 but I have used some DBCC commands in SQL Server 2005 too. (E.g: DBCC IND, DBCC PAGE)


Since these are undocumented onces you have to use at your risk and no support available from Microsoft.


Useful Undocumented DBCC Commands

Using Backslash (“\”) in SSIS Expressions

I wanted build an expression to make a folder name dynamically. (Basically expressions are anyway dynamic in nature)

So I simply used the “\” as end of my expression as stated below;

@[User::Differential_backup_path] + @[User::database_name] + "Differential\"

It gave an error;

Then I tried various methods including CHAR(47) which is an ASCII value for the backslash.

After spending some time on research in google, I found that you need to use two backslashes. (“\\”)

So the correct expression would be;

@[User::Differential_backup_path] + @[User::database_name] + "Differential\\"

Identify out of synch subscriptions

One of the main tasks of DBA is to monitor replication status. There may be situations where subscriptions may out of synch due to various reasons. If this happened, DEV and QA will face issues and they will start communicate with DBAs to verify the replication status. If you’ve not setup proper monitoring mechanism to see replication staus this is the usual story.


However if you have already setup monitoring mechanism the DBAs will identify the issues upfront without making panic on DEVs and QAs.

This blog is on how to identify out of synch subscriptions especially in transactional replication.

Policy-based Management in SQL Server 2008

Policy-base Management is a new feature in SQL Server 2008 that helps SQL Server administration. It allows Database Administrators to manage SQL Server instances by intent through clearly defined policies, thus reducing the potential for administrative errors. The policy-based framework implements the policies behind the scene with a Policy Engine, SQL Server Agent jobs, SQLCLR, DDL triggers and Service Broker. Policies can be applied or evaluated against a single server or a group of servers, thus improving the scalability of monitoring and administration.

I found couple of interesting articles on this subject and I'm posting them here for you're reference. 

ScreenCast 

Dedicated blog for Policy-based Management

Inserting multiple rows in single INSERT

Do you know there is a new T-SQL command in SQL Server 2008 which facilitates you to insert several rows in single INSERT statement? This is really a cool feature to me. As a DBA I want to write many data patches to populate data specially for lookup data. Let me say, you want to insert several records into a table. You will have to write several INSERT statements to accomplish this task in prior versions of SQL Server. Now in SQL Server 2008 it is very easy. So I thought of posting this blog for your reference. Read the full blog to see this cool command. 


I want to write a data patch to populate the table, dbo.CountryRegion.



IF OBJECT_ID('dbo.CountryRegion','U') IS NOT NULL
DROP TABLE dbo.CountryRegion

CREATE TABLE dbo.CountryRegion
(
CountryRegionCode char(2)
,[Name] varchar(100)
)

Tables with no Clustered Index

Most of the time it is a good practice to have a Clustered Index on each table. However I've seen numerous instances where there are tables without Clustered Index. The following code will help 
you to identify the list of tables which do not have Clustered Index.

Query best practices

In this blog I'm stating several best practices when writing SQL queries in MS SQL Server. They are;

• Choose the most appropriate data type
• Write correctly formed queries
• Return only the columns/rows needed
• Fully qualified object names
• Avoid long actions in triggers
• Avoid expensive operators such as NOT LIKE
• Minimize cursor use
• Limits query and index hints
• Avoid implicit or explicit functions in WHERE clause
  

Name begins with sp_

If your stored procedures names begin with the sp_ prefix and is not in the master database, you will see SP:CacheMiss before the cache hit for each execution even if the stored procedure call is owner qualified. The reason for this is that the sp_ prefix tells SQL Server that the stored procedure is a system stored procedure. System stored procedures have different name resolution rules. With system stored procedures, SQL Server will look first in the master database if the call is not database qualified. Then will look in the current database. To avoid this additional work, do not use stored procedure names that begin with sp_

Loop through all the databases in a server by using SSIS

As a database developer or administrator you may need to loop through all the databases in a server and perform various tasks for each database. There are various methods you can perform this in T-SQL. However here I’m presenting a simple solution by using an SSIS package.Let me go through the solution and explain how it is implemented.

Below are the steps;

1. Start SQL Server Business Intelligence Development Studio.
2. Create an Integration Project. Name the directory and project name.
3. Rename the package to EachDatabase.dtsx by using solution explorer.

4. Create an OLE DB connection in Connection Manager to the target server. Set the RetainSameConnection property to True in connection manager's property window.
5. Add two Execute SQL Tasks, Script Task and For Loop Container to the Control Flow section.
6. Connect them as shown below;

7. I have given meaningful names for each component.
8. You may be getting errors and warnings in Execute SQL Tasks and ignore them for the movement.
9. You need to add four variables to the package. Scope of the variables will be different according to the place where they use.
10. See the diagram below for the variables.

11. Max_ID and Min_ID variables have the package scope while “database_name” and “loop” are having For Loop Container scope.
12. Now let’s configure Execute SQL Tasks, For Loop Container and Script Task to get the final output.

Configure “Get all databases” Execute SQL Task

Change the properties mentioned below.

1. Name, Description (You can give any name)
2. ResultSet: Single row
3. ConnectionType: OLE DB
4. Connection: Select the connection you’ve created in the beginning
5. SQLSourceType: Direct input
6. SQL Statement: Add the SQL statement mentioned below;

7. Set the variables “Result Set”. This is the place where you initialize Max_ID and Min_ID variables. Max_ID and Min_ID are not the database id rather it is an identity value of the temp table.

Configure For Loop Container

1. Double click on the container and you will get the For Loop Editor as shown below;

2. Set the For Loop Properties like above.

Configure “Get database name” Execute SQL Task

1. Double click on “Get database name” Execute SQL Task.
2. Set the property values;
1. ResultSet: Single row
2. ConnectionType: OLE DB
3. Connection: (The connection you’ve created)
4. SQLSourceType: Direct input
5. SQLStatement: [select dbname from #databases where id=?]

3. Set the parameter mapping. Variable “loop” will be the input and by using that value you get the database name.

4. Setting Result Set. Assigning database name to the variable, “User::database_name”

Configure “Display database name” Script Task

1. Double click on the Script Task.
2. Select Script tab in the left pan.
3. Enter User::database_name in ReadWriteVariables section.
4. Click on Design Script button.
5. Add the VB.NET code mentioned below;

Dim db_name As String

db_name = Dts.Variables("User::database_name").Value.ToString()

MsgBox(db_name)

6. Save and exit.

Now the package is all set for the final execution. Start the package and see its execution. Database name will be displayed in a message box and when you click on “OK” the next one will be displayed.

The advantage of this solution is to define the databases which you are going to loop through from the beginning.

I have used SQL Server 2005 BIDS (Business Intelligence Development Manager) to demonstrate the solution.

More about ORDER BY clause

Every one knows the purpose and the use of ORDER BY clause in SQL Server, but most may not know how ORDER BY works, in-side SQL Server. The implementation of T-SQL is based on set theory. Each table is considered as a set. Let’s take an example of Customer and Order tables. They are actually considered as two sets. The records of the table are identical to members or elements in sets. The T-SQL statements will not work as sequential language execution. Consider the T-SQL statement below.

use AdventureWorks

GO

SELECT TOP 100 SH.CustomerID,SH.SalesOrderNumber,SD.OrderQty,SD.UnitPrice FROM Sales.SalesOrderHeader SH

INNER JOIN Sales.SalesOrderDetail SD

ON SH.SalesOrderID=SD.SalesOrderID

ORDER BY SH.CustomerID

When you execute the query above, it will not execute sequentially within the SQL Server. Even though the SELECT statement is appeared first it will not execute as the first statement. Instead it will execute at a later part of the query (The execution order of a T-SQL query is beyond the scope of the article). During execution, T-SQL query will go through several virtual tables. The final result set of the query also displays as a virtual table.

But when you include the ORDER BY clause in your query the final result will not be a table, instead it returns as a cursor output. Because, a set does not have predetermined order of its members. It is a logical collection of members and the order of the members should not matter. In other words you can not perform an ORDER BY in a set simply by using set concepts. This is the reason the SQL Server uses cursors for this operation. ORDER BY is a costly operation to SQL Server (The use of cursors is anyway a costly operation).

Consider the T-SQL query below.

--Query1

USE AdventureWorks

GO

SELECT SalesOrderID,OrderQty,ProductID,UnitPrice FROM Sales.SalesOrderDetail

ORDER BY SalesOrderID

GO

Look at the execution plan of this query.

You may wonder why the execution plan does not contain the SORT operator. The obvious reason is SalesOrderID is the clustered index of the table, Sales.SalesOrderDetail which means the data is physically ordered by using the key of SalesOrederID column. So there will not be any additional cost for this sorting.

Let’s look at the query below;

--Query2

USE AdventureWorks

GO

SELECT SalesOrderID,OrderQty,ProductID,UnitPrice FROM Sales.SalesOrderDetail

ORDER BY UnitPrice

GO

Look at the execution plan of this query.

Now you can see the SORTing is happening here and it consumes 71% percent from the total cost of the query.

Consider this query;

--Query3
USE AdventureWorks

GO

SELECT SalesOrderID,OrderQty,ProductID,UnitPrice FROM

(SELECT SalesOrderID,OrderQty,ProductID,UnitPrice FROM Sales.SalesOrderDetail

ORDER BY UnitPrice) A

I have used ORDER BY clause withing the sub-query, let’s execute this and see the result.

What do you expect as the result of this query?

Think before it executes.

Below is the output;

Msg 1033, Level 15, State 1, Line 3

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Were you able to figure out why we are getting this error?

As I already explained, the query will execute by creating several virtual tables, keeping the intermediate results and final result also a virtual table. But ORDER BY clause works quite differently. It returns a cursor. So the sub-query is returning a cursor result while outer query is implementing a virtual table. This is the reason why you can not use the ORDER BY clause in sub-queries, views, inline functions, etc (as mentioned in the error message).

The error message gives a hint to resolve the issue.

Consider the query below;

--Query4

USE AdventureWorks

GO

SELECT SalesOrderID,OrderQty,ProductID,UnitPrice FROM

(SELECT TOP 100 PERCENT SalesOrderID,OrderQty,ProductID,UnitPrice FROM Sales.SalesOrderDetail

ORDER BY UnitPrice) A

Now you can see the result of the query and the execution plan.

The difference of query 3 & 4 is the TOP 100 PERCENT statement. When you use the TOP operator (which is not a relational operator rather it is a T-SQL specific) the ORDER BY clause will not use a cursor when implementing. It uses a virtual table thus it prevents the above error and also the cost is very low with compared to the query 2.

Conclusion

As a rule of thumb it is a good practice to avoid ORDER BY clause unless you really need the rows sorted. Use ORDER BY clause in sub-queries with TOP 100 PERCENT if you really need it. A query with ORDER BY clause can not be used as a table expression that is, a view, inline table-valued function, sub query, derived table, or common table expression (CTE).

SQL version used for this article is, Microsoft SQL Server 2005 - 9.00.3073.00 (Intel X86) Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)