List all Stored Procedures,Tables or Views in SQL Server 2000/2005

Some times we need to take a print of all the stored procedures, tables or views from our database in SQL Server 2000/2005. For that purpose first we need to list all the user created stored procedures,tables or views. You can make use of the following queries.

1. To List all user created Stored Procedures
Select name From sys.objects Where type = 'P'

2. To List all User created Tables
Select name From sys.objects Where type = 'U'

3. To List all User Created Views
Select name From sys.objects Where type = 'V'

4. You can retrieve data by giving your own filter values, just list all columns to find what is needed
Select * From sys.objects

Now you can create interesting queries for various analysis or just for information purpose
Example:
Get me all the user stored procedures that are created after 20th jan 2009
Select name From sys.objects Where type = 'P' And create_date > '1/20/2009'

So now you can have your own queries...
:)

Note:
In SQL Server Query Analyser (SQL Server 2000) or SQL Server Management Studio Express (SQL Server 2005), you can save the result of the sql query to a file or can view as text.

Right click in the query window and select "Results To". Now you have three options
1) Results to Text, 2) Results to Grid, 3) Results to File
Select your choice.

Dealing with null in sql queries in SQL Server 2000/2005

We may need to deal with null values often when we write queries in SQL Server 2000/2005. Here I provide some examples that are commonly used. One of my friend asked how to check whether a field is null, he tried col1=null but didn't worked. So I thought I will share this small but useful examples to all.

1) To check whether a field/column value is null
Just check if it is null

Eg:
select * from tbl where col1 is null;

2) To check whether a field/column value is not null
Just check if it is not null

Eg:
select * from tbl where col1 is not null;

3) To set a field/column value to null (update a field value to null)
Just set it to NULL

Eg:
update tbl set col1= NULL Where val1 = 1;

Another important Note:
When we want to take the maximum value (max) or the total(sum) using aggregate functions, it is always good to check for null values.If the table do not have any records or the result of the query is null, this may result in error.

select @nextVal = max(col1) + 1 from table1; --Not preffered way

So we will try
select @nextVal = max(isnull(col1,0)) + 1 from table1; --Not always success
Hey, this is also not working...

Ok finally, the right way
select @nextVal = isnull(max(col1),0) + 1 from table1;

To find the sum
select @Total = isnull(sum(col1),0) from table1;

Happy Coding ....
:)

Calling an exe from Windows Service

Some times we may need to call an exe from our windows service program. The following code can be used to call an exe from an appication. The sample code is for VB.NET applications. Here the calculator (calc.exe) is called.

Dim startInfo As System.Diagnostics.ProcessStartInfo
Dim pStart As New System.Diagnostics.Process

'Start the process

startInfo = New System.Diagnostics.ProcessStartInfo("calc.exe")

pStart.StartInfo = startInfo

pStart.Start()

pStart.WaitForExit()

pStart.Close()

Here
pStart.WaitForExit() makes your application wait till the called exe is closed.
pStart.Close() will free all the resources that are associated with this component.

This will work if you are using a windows Application. But in a windows service this will not work straight, you need to change some setting for the windows service. A service won't be able to show the GUI of an application, so if the application you are calling has any GUI you won't be able to see it. But the process will get kicked off and you can see the evidence in TaskManager. But we can enable the service to popup the UI by enabling this settings.

Allow Services to interact with desktop

1. Start > Run > Services.msc
2. Locate the services which you have created
3. Right click and select properties.
4. Select "Log On" tab on the top of the screen
5. Select "Allow Services to interact with desktop" check box.
6. Click on "Apply" and start the services.

If you want to kill an application during Windows Service Stop, try the following code

Dim myProcess As Process

Dim myProcesses As Process() = Process.GetProcessesByName("calc")

For Each myProcess In myProcesses

myProcess.Kill()

Next