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

Bill Gates turns 52 today :) Happy Birthday BillG.

I removed all of my VZ friends blog from my blogroll and have added onto my bloglines account.

Theory: :r -- parses additional T-SQL statements and sqlcmd commands from the file specified by into the statement cache. In this article we would see the usage of :r as well as handling spaces in SQLCMD. In SQL Mgmt Studio: Step 1: 01VariableInitialization.sql :setvar filepath "C:\Vadivel\SQL Related\Scripts\sqlcmd" :r $(filePath)\02TableCreation.sql Step 2: 02TableCreation.sql Create table tblTest ( Sno int identity, FName varchar(20) ) Go :r $(filePath)\03InsertScripts.sql Step 3: 03InsertScripts.sql Insert into tblTest (Fname) values ('alpha') Explanation of each file: 01VariableInitialization.sql -- In this file we create a scripting variable 'filePath' which will hold the path of the .sql files which we use for this demo. Then it executes 02TableCreation.sql. 02TableCreation.sql -- In this, we create tables of our choice. Also we make use of the scripting variable created in the previous file here to call another .sql file to insert records into th...

This example demonstrates the way to create a variable and make use of it for multiple purpose. Actually in this example we would see how to create a variable and append strings into it. Lets create two Database with slight difference in the name. For example, DB1 and DB2. Then create a table in each DB and populate few records into it. ---Code snippet which needs to be run in Mgmt Studio starts here--- Use master go Create Database DB1 go Use DB1 go Create table t1 (a int) go insert into t1 values (1) insert into t1 values (2) insert into t1 values (3) go Create database DB2 go Use DB2 go Create table t2 (Num int) go insert into t2 values (4) insert into t2 values (5) insert into t2 values (6) go --Code snippet to be run in Mgmt studio ends here--- Now open command prompt and connect into the DB. Step 1: SQLCMD -U sa -P hot -S VADIVEL Step 2: Press Enter Now lets create a variable by name "dbname" and assign 'DB' as the string to it. Step 3: :setvar dbname DB Now l...

If we drop a column it gets dropped but the space which it was occupying stays as it is! In this article we would see the way to reclaim the space for a table which has a non-clustered Index. Create a table with non-clustered index in it: Create Table tblDemoTable_nonclustered ( [Sno] int primary key nonclustered, [Remarks] varchar(5000) not null ) Go Pump-in some data into the newly created table: Set nocount on Declare @intRecNum int Set @intRecNum = 1 While @intRecNum Begin Insert tblDemoTable_nonclustered (Sno, Remarks ) Values (@intRecNum, convert(varchar,getdate(),109)) Set @intRecNum = @intRecNum + 1 End Check the fragmentation info before dropping the column: DBCC SHOWCONTIG ('dbo.tblDemoTable_nonclustered') GO Output: DBCC SHOWCONTIG scanning 'tblDemoTable_nonclustered' table... Table: 'tblDemoTable_nonclustered' (1781581385); index ID: 0, database ID: 9 TABLE level scan performed. - Pages Scanned................................: 84 - Extents Scanned......

If we drop a column it gets dropped but the space which it was occupying stays as it is! In this article we would see the way to reclaim the space for a table which has a clustered Index . Create a table with clustered index in it: Create Table tblDemoTable ( [Sno] int primary key clustered, [Remarks] char(5000) not null ) Go Pump-in some data into the newly created table: Set nocount on Declare @intRecNum int Set @intRecNum = 1 While @intRecNum Begin Insert tblDemoTable (Sno, Remarks ) Values (@intRecNum, convert(varchar,getdate(),109)) Set @intRecNum = @intRecNum + 1 End If it's SQL 2000 or earlier: DBCC SHOWCONTIG ('dbo.tblDemoTable') -- Displays fragmentation information for the data and indexes of the specified table Go Output: DBCC SHOWCONTIG scanning 'tblDemoTable' table... Table: 'tblDemoTable' (1717581157); index ID: 1, database ID: 9 TABLE level scan performed. - Pages Scanned................................: 80 - Extents Scanned......................

To list all available SQLCMD Scripting variables, do the following: Step 1: Go to DOS prompt and open up SQLCMD Step 2: type :listvar which would list all SQLCMD scripting variables. In the screenshot you can see that the SQLCMDEditor and SQLCMDINI variable which we overwrote in the previous posts here and here are displayed. Almost all of the other variables are self-explanatory :) Creating our own local variables: :setvar DB1 testBed :setvar DB2 Adventureworks use $(DB1) go Select getdate(); go use $(DB2) go select top 5 city from person.address go Point to note: 1. If you execute :listvar again you can find these newly created variables getting listed there now. But please note that these local variables would be alive only till the life of the current session. i.e., If you exit out of SQLCMD once and come back and type :listvar these variables would be missing there. 2. Always the variables which we create will be in Uppercase only . Just try creating something like this: :s...

There are instances where we might need to run some default scripts on a specified server once SQLCMD gets connected. It can be achieved in just three steps as explained below: Step 1: Create a script file which you wanted to fire when SQLCMD gets connected to your SQL Server. For keeping the example simple, I used the following line and saved it as SqlCmdStartUpScripts.sql print 'Welcome today''s date is: ' + (convert(varchar, getdate())) Step 2: Open DOS prompt and type set sqlcmdini=c:\vadivel\sql related\scripts\SqlCmdStartUpScripts.sql Step 3: Then type SQLCMD and press Enter . Refer the below screenshot for the sample output.

From my previous posts one can understand that it is possible to write SQL queries directly in command prompt with the help of SQLCMD utility. Now let's assume we have typed a 'big' query and there is a typo there! Instead of going back and forth to edit it in command prompt won't it be easy if we are able to open the query in an editor and make the corrections there? Yes its possible in SQLCMD. All we need to do is type ed and it will open up the last command/query in a text editor. FYI the default editor is Edit (the command line editor of MS DOS). Step 1: Open up SQLCMD and connect to your SQLServer Step 2: type any query of your choice Step 3: type ed Step 4: The query would have opened in the 'EDIT' utility of DOS. Once you are done with the change, save and exit from that. Step 5: type go and press enter Can I make 'ed' to open up notepad or any editor of my choice? Yes it's possible. Step 1: Open DOS prompt Step 2: Type set sqlcmdedito...

If you haven't gone through the first two posts about SQLCMD I would strongly recommend to go over it here and here before proceeding further :) 1. Executing a script file from SQLCMD... i. Create a script file by typing in the following line and save it as Message.sql print 'Welcome today''s date is: ' + (convert(varchar, getdate())) ii. Now goto command prompt and type: SQLCMD -i Message.sql here, -i is the switch to specify the input file name. 2. Executing series of script files (sample) Lets create couple of .sql files and then see how to execute them in order from command prompt. Please note that i am just showing an example here :) there are better methods of doing the same which I would explain later in the series! i) Copy paste the below script and name it as 01TableCreation.sql Create table tblTest ( Sno int identity, FName varchar(20) ) Go ii) Copy paste the below script and name it as 02InsertRecords.sql Insert into tblTest (Fname) values ('a'...

First get connected into the DB Server using SQLCMD using either the windows authentication or SQL authentication as explained in the previous post . Then on the prompt you can type in the TSQL queries directly and press enter. In the next line, say Go and press enter to execute the query and see the result. See below an example to display the current datetime. After displaying the result the cursor would stand on the prompt expecting for further queries from us :) Once you are done you can 'exit' out of sqlcmd utility like shown below: By default, it get's connected to the default database of that login only. One can make use of -d switch to connect to a DB of their choice . Please refer the below sample where I make use of Adventureworks DB and query a table. What is the difference between -q and -Q switch? -q is for running queries from SQLCMD. -Q is for exitting from SQLCMD immediately after executing a given query. SQLCMD -q "Select getdate()" This query ...

“SQLCMD” is a command line tool which was shipped by Microsoft along with SQL Server 2005. Previously SQL Server was having ISQL and OSQL as its command line utility. SQLCMD is replacing both of them (i.e., ISQL is not there in SQL Server 2005 RTM version. OSQL would also be eventually removed!!). When “SQLCMD” is run from the MS-DOS command prompt, it uses the OLE DB provider to execute the given queries. SQLCMD has batch and interactive modes. 'Batch mode' can be used mainly for scripting and automation tasks, while 'Interactive mode' is for firing ad-hoc querys. i) To list all the parameters supported by SQLCMD utility, run the following in command prompt SQLCMD /? ii) To connect to SQL Server using SQL Authentication SQLCMD -U username -P yourpassword -S Servername Once we press the enter key. If the UID/PWD is valid it would prompt you to enter the t-sql query to execute. If at all the UID/PWD combination is wrong it would throw an error similar to the one shown be...

Some pointers: 1. NewSequentialID() and NewID() both generates the GUID of datatype of uniqueidentifier. 2. NewID() generates the GUID in random order 3. NewSequentialID() generates the GUID in sequential order. 4. NewSequentialID() can be used ONLY in default clause of a table. 5. NewSequentialID() is easily predictable 6. So if security is a major concern then go for NewID() instead of NewSequentialID(). Example to demonstrate NEWSEQUENTIALID(): Create table #tblDemoSequentialID ( Column1 uniqueidentifier default NewID(), Column2 uniqueidentifier default NewSequentialID(), Fname varchar(30) ) Pump-in few dummy records: Insert into #tblDemoSequentialID (Fname) values ('Vadivel') Insert into #tblDemoSequentialID (Fname) values ('Rajinikanth') Insert into #tblDemoSequentialID (Fname) values ('Sivaji') In this query 'Column1' would demonstrate that the 'NEWID' has generated GUID in random fashion. Also 'Column2' would contain GUID in Sequen...