Sunday, November 25, 2007

Get TIME alone from a given date

I see this to be very frequently asked question in dotnetspider.com! Solution to this is to make use of CONVERT function in SQL Server.

--Query will fetch the time portion alone
Select Convert(Varchar, Getdate(), 108)

--Query will fetch the date portion alone.
Select Convert(Varchar, Getdate(), 101)

The last parameter of Convert function is StyleId. Go through books online to know the complete list of parameters and its corresponding output format.

Friday, November 23, 2007

Should we upgrade to SQL 2005 or wait for SQL Server 2008?

To my knowledge, there are companies which still works on SQL Server 7.0 and SQL Server 2000. But in the last quarter of 2005, Microsoft released SQL Server 2005. Though I have been using that product personally since that time and officially for more than one year now. I am not too sure whether everybody has migrated to SQL Server 2005!

That being a case SQL Server 2008 (code named: Katmai) is around the corner now :) Hmm we need to wait and see how many of them migrate immediately.

Wednesday, November 21, 2007

List tables which are dependent on a given table - SQL Server 2005

Option 1: Right-click on a table and choose 'View Dependencies'.

Option 2: For some reason if you want to do it programmatically check out the below code snippet

Select
S.[name] as 'Dependent_Tables'
From
sys.objects S inner join sys.sysreferences R

on S.object_id = R.rkeyid
Where
S.[type] = 'U' AND
R.fkeyid = OBJECT_ID('Person.StateProvince')


here, replace Person.StateProvince with your table name.

Monday, November 19, 2007

List the modified objects in SQL Server 2005

For viewing the modified date of Stored Procs and UDF alone:

Select
Routine_name, Routine_Type, Created, Last_altered
From Information_schema.routines
Where
Routine_type in ('PROCEDURE', 'FUNCTION')
Order by
Last_altered desc, Routine_type, Routine_name


For viewing the modified date of Stored Procs, UDF and Views:

We can query 'Sys.Objects' table and find out the list of Stored procs, UDFs, Views etc., which have got modified.

Code snippet:
Select
[name] as 'Object Name',
[type] as 'Object Type',
create_date,
modify_date
From sys.objects
Where [type] in ('P', 'FN', 'TF', 'V')
Order by Modify_Date desc, [type], [name]


The above query will list all 'SPs', 'UDFs' and 'Views' in the current database with its 'Created date' and 'Modified date'. We can further finetune this code to match our exact need!

For triggers
Check out create_date and modify_date columns in sys.triggers.
select * from sys.triggers

Sunday, November 18, 2007

Codd's Rule and Current RDBMS Products ...

I was discussing with few of my friends on the topic 'Codds rule'. I heard them say that 'SQL Server' and 'Oracle' supports all of the 12 codd's rule but DB2 doesn't support few of them!! I was surprised and then later got confused myself :)

Actually to my knowledge there is no RDBMS product (be it, Microsoft SQL Server or Oracle or DB2) which satisfies all of the 12 rules of CODD.

Hopefully in few days i will write about my knowledge on this subject and leave it open for others comments :)

Expansion for the word CURSOR in SQL Server

Cursor = Current Set Of Records

Wednesday, November 14, 2007

SQLCMD -- Part IX (Batch files)

Using SQLCMD to execute script files easily in different environments

Assume we have few script files (.sql) which needs to be run in multiple SQL Servers. Hope you would accept that's real pain to connect into different servers from SQL Management Studio and then execute the scripts one after the other.

One of the easiest ways of doing it is by making use of the SQLCMD utility of SQL Server 2005.

Step 1: Lets create few dummy script files for demo purpose.

File1: 01TableCreation.sql

Create table tblTest
(
Sno int identity,
FName varchar(20)
)
Go

File2: 02InsertRecords.sql

set nocount on

Insert into tblTest (Fname) values ('alpha')
Insert into tblTest (Fname) values ('beta')


File3: 03StoredProcedures.sql

Create proc usp_GetAllTblTest
as
Select sno, fname from tblTest
go


Step 2: Create a batch file and call these .sql files in order.

File4: DBInstallationScripts.bat

sqlcmd -U %1 -P %2 -S %3 -d %4 -i "C:\Vadivel\SQL Related\Scripts\sqlcmd1\01TableCreation.sql"
sqlcmd -U %1 -P %2 -S %3 -d %4 -i "C:\Vadivel\SQL Related\Scripts\sqlcmd1\02InsertRecords.sql"
sqlcmd -U %1 -P %2 -S %3 -d %4 -i "C:\Vadivel\SQL Related\Scripts\sqlcmd1\03StoredProcedures.sql"


Step 3: Execute the batch file

From the command prompt (Start >> Run >> Cmd) do the following:

c:> DBInstallationScripts DBUserName DBPassword DBServerName Databasename

Please note we are passing the database username, password, servername and the database to which we need to connect from the batch file. That way it would replace %1, %2, %3 and %4 in the batch file while executing.



So this way, same set of scripts can be executed in different environments like 'Development', 'Testing', 'Production' etc., with ease.

Hope this helps!