There are cases when SQL developers want to filter the results of Procedures Output. This requires inserting the results of the procedure in the table. Here is how it can be done In the above example the temp table is created with the same columns as that of the procedure output, the Procedure is executed [...]
Displaying the most recent of 45 posts written by
Vivek
Getting the list of tables and Columns that are embedded in a view ,function or a procedure is simple sp_depends ‘viewName or ProcedureName‘ Documented in MSDN http://msdn.microsoft.com/en-us/library/ms189487.aspx
The Problem occurs when an aggregate function(max,sum,avg..) exists on null values. Trying to get rid of the null values may be the solution but in certain cases..you may need the extra data relying on the null values. So whats the solution? Append set ANSI_WARNINGS OFF on the beginning of the sql statement. When using the [...]
Sql Server 2008′s new TOP statement could help update the Table in batches Consider the following Example The above code illustrates how the batchUpdate can be done using Top(). The top 100 records are updated depending on the criteria where fullname is null so that the same records dont get updated again
Here are the List of features that are deprecated in Sql server 2008 http://msdn.microsoft.com/en-us/library/ms143729.aspx
I constantly keep querying the Database sometimes for checking the metadata in the DatawareHouse. Lately I realised that have been executing the same long sql statements everyday. So is there a better way of Organising all my Favourite SQL statments or procedures ? This is what I ended up with. Create a Table The table [...]
Ran a Job yesterday to check how the SQL agent Handles the DayLight Savings Created the following Table Created the Following procedure to enter the current Date and Ran the Job every minute from March 28th 2009 22:50 to Mar 29th 2009 3:00 AM Results: There was no 01:00 AM on March 29th !!!!. So [...]
The Following Script will output the List of Databases in the SQL server and gets the Last Date the Databases were backed up select Convert(Varchar(25),server_name) as ‘Server Name’ ,convert(varchar(25),database_name) as ‘Database’ ,max(backup_finish_date) as ‘Last_backup_date’ from msdb..backupset Where database_name in (select name from master..sysdatabases) and server_name = @@servername group by server_name,database_name order by Last_backup_date DESC
SELECT name, create_date, datepart(dy,create_date) as CreatedDayOfYear, modify_date, datepart(dy,modify_date) as ModificationDayOfYear FROM sys.sql_modules JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND TYPE = ‘P’ order by datepart(yyyy,modify_date) desc, datepart(dy,modify_date) desc, name;
Here is the Sql function that will Proper Case any column in a table set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO create function [dbo].[ProperCase](@Text as varchar(8000)) returns varchar(8000) as begin declare @Reset bit; declare @Ret varchar(8000); declare @i int; declare @c char(1); select @Reset = 1, @i=1, @Ret = ” while (@i <= len(@Text)) select [...]
