Vadivel's blog

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.

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.

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.

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. sel...

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 :)

Cursor = Cur rent S et O f R ecords

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.s...