Find a Table Name :

To find table_name used in multiple procedures,



Create Procedure [dbo].[Proc_Table_Name_Search] (@table_name nvarchar(50))
as
Begin

select top 0 (left( cast(' ' as varchar(max)) + replicate(' ',450) ,450)) as text into #t1
select top 0 (left( cast(' ' as varchar(max)) + replicate(' ',100) ,100)) as sp_name,9999 as Line_number into #t2
declare @name nvarchar(50)
declare @query nvarchar(max)
declare @no int
declare test_cur cursor for
select name from sys.objects where type='p'

open test_cur
fetch next from test_cur into @name
while @@fetch_status=0
begin

truncate table #t1

insert into #t1
exec sp_helptext @name

print @name

    declare @line nvarchar(max)
    set @no=1
    declare test1_cur cursor for
    select text from #t1

    open test1_cur
    fetch next from  test1_cur into @line
    while @@fetch_status=0
    begin
  
    if (@line like '%'+@table_name+'%')
    begin
    insert into #t2 values(@name,@no)
  
    end
    set @no=@no+1
    fetch next from  test1_cur into @line
    end
    close test1_cur
    deallocate test1_cur

fetch next from test_cur into @name
end

select sp_name,Line_number from #t2

close test_cur
deallocate test_cur

End


exec Proc_Table_Name_Search '<sample_table_name>'

Image Upload and View in SQL Server

Step 1:Create a table like this..

CREATE TABLE [dbo].[image_table](
    [images] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


Step 2:
Create a format file like this.

       open a notepad file and type this below after save as .fmt format


---------------------------------------------------------------------------------
8.0
1
1   SQLIMAGE   0   3935      ""      1   picture SQL_Latin1_General_CP1_CI_AS

--------------------------------------------------------------------------------

Step 3: In query analyser  to type the beloy query to upload the image file to database

BULK INSERT image_table FROM 'c:\tmp\Sample.jpg'
WITH (
   FORMATFILE = 'c:\tmp\tam.fmt'
)


Step 4:

If you want to view the image just run the bcp below


bcp ICC_WORLDCUP..image_table out c:\t.jpg -T -N -f tam.fmt

Index REBUILDING

DECLARE @Table VARCHAR(255)
DECLARE @indexes VARCHAR(255)
DECLARE @cmd VARCHAR(1000)

DECLARE DatabaseCursor CURSOR FOR

select a.name,c.name from  sys.objects a left outer join (select distinct object_id,index_id from sys.index_columns) b
on a.object_id=b.object_id
left outer join (select  distinct index_id,name,object_id from sys.indexes) c on b.index_id=c.index_id and a.object_id=c.object_id
where type='u' and a.name not in('tam_rawdata','tam_viewership','tg_master','TAM_RAWDATA_TEMP','tam_viewership_file_temp','tam_viewership_temp_2',
'sysdiagrams','tam_viewership_temp','users_subscription','LOGIN_INFO','channel_target','Audit_Log_Master','tam_error_log','TMP_VIEWERSHIP')


OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Table, @indexes
WHILE @@FETCH_STATUS = 0
BEGIN




SET @cmd = 'ALTER INDEX ['+@indexes+'] ON [dbo].['+@Table+'] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )'


exec(@cmd)


FETCH NEXT FROM DatabaseCursor INTO @Table, @indexes

end
CLOSE DatabaseCursor 
DEALLOCATE DatabaseCursor

Cross Apply and Outer Apply in SQL Server 2005

In this article I would talk about the New APPLY operator in SQL Server 2005. As usual I would provide a working sample / code snippet for better understanding.

Extract from BOL:

The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.

Lets see this in action:

Let us create couple of tables and populate with some test data.

Create Table EmployeeMaster
(
EmpId int,
EmpName varchar(50),
Age int,
Grade varchar(5)
)Go

Create Table Loan
(
LoanId int,
EmpId int,
LoanAmount int,
Month varchar(3)
)
Go

Insert Test data:

Insert into EmployeeMaster values (1,'Vadivel',29,'I')
Insert into EmployeeMaster values (2,'Sai',28,'I')
Insert into EmployeeMaster values(3, 'Velias',20, 'II')

Insert into Loan values(1,1,2000,'Jan')
Insert into Loan values(1,2,1000,'Feb')
Insert into Loan values(1,1,1000,'Feb')
Insert into Loan values(1,1,100,'Mar')
Insert into Loan values(1,2,1700,'Jun')
Insert into Loan values(1,1,800,'Aug')

Cross Apply:

Let see how to make use of Cross Apply operator in the below code snippet

Select E.EmpName, A.LoanAmount from EmployeeMaster E
CROSS APPLY
(
Select top 2 LoanId, LoanAmount from Loan L
where L.EmpID=E.EmpId
order by L.LoanAmount desc
) A

Output:

Vadivel 2000
Vadivel 1000
Sai 1700
Sai 1000

Outer Apply:

Let see how to make use of Outer Apply operator in the below code snippet

Select C.EmpName, A.LoanAmount from EmployeeMaster E
OUTER APPLY
(
Select top 2 LoanId, LoanAmount from Loan L
where L.EmpID=E.EmpId
order by L.LoanAmount desc
) A

Output:

Vadivel 2000
Vadivel 1000
Sai 1700
Sai 1000
Velias NULL

Get All Stored Procedure in one sort

create procedure Test_sp_generation
@columnname sysname = NULL 
as 
 
set nocount on 
 
declare @dbname sysname 
,@objid int 
,@BlankSpaceAdded   int 
,@BasePos       int 
,@CurrentPos    int 
,@TextLength    int 
,@LineId        int 
,@AddOnLen      int 
,@LFCR          int --lengths of line feed carriage return 
,@DefinedLength int 
 
/* NOTE: Length of @SyscomText is 4000 to replace the length of 
** text column in syscomments. 
** lengths on @Line, #CommentText Text column and 
** value for @DefinedLength are all 255. These need to all have 
** the same values. 255 was selected in order for the max length 
** display using down level clients 
*/ 
,@SyscomText nvarchar(4000) 
,@Line          nvarchar(255) 
 
select @DefinedLength = 255 
select @BlankSpaceAdded = 0 /*Keeps track of blank spaces at end of lines. Note Len function ignores 
                             trailing blank spaces*/ 
CREATE TABLE #CommentText 
(LineId int 
 ,Text  nvarchar(255) collate database_default) 
 

        declare ms_crs_syscom  CURSOR LOCAL 
        FOR select text FROM SysComments
WHERE ID
      IN
      (
       SELECT Id
       FROM SysObjects
       WHERE XType = 'p'
       )
        FOR READ ONLY 
 
   
/* 
**  else get the text. 
*/ 
select @LFCR = 2 
select @LineId = 1 
 
 
OPEN ms_crs_syscom 
 
FETCH NEXT from ms_crs_syscom into @SyscomText 
 
WHILE @@fetch_status >= 0 
begin 
 
    select  @BasePos    = 1 
  select  @CurrentPos = 1 
    select  @TextLength = LEN(@SyscomText) 
 
    WHILE @CurrentPos  != 0 
    begin 
        --Looking for end of line followed by carriage return 
        select @CurrentPos =   CHARINDEX(char(13)+char(10), @SyscomText, @BasePos) 
 
        --If carriage return found 
        IF @CurrentPos != 0 
        begin 
            /*If new value for @Lines length will be > then the 
            **set length then insert current contents of @line 
            **and proceed. 
            */ 
            while (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength 
            begin 
                select @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded) 
                INSERT #CommentText VALUES 
                ( @LineId, 
                  isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N'')) 
                select @Line = NULL, @LineId = @LineId + 1, 
                       @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0 
            end 
            select @Line    = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'') 
            select @BasePos = @CurrentPos+2 
            INSERT #CommentText VALUES( @LineId, @Line ) 
            select @LineId = @LineId + 1 
            select @Line = NULL 
        end 
        else 
        --else carriage return not found 
        begin 
            IF @BasePos <= @TextLength 
            begin 
                /*If new value for @Lines length will be > then the 
                **defined length 
                */ 
                while (isnull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength 
                begin 
                    select @AddOnLen = @DefinedLength - (isnull(LEN(@Line),0) + @BlankSpaceAdded) 
                    INSERT #CommentText VALUES 
                    ( @LineId, 
                      isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N'')) 
                    select @Line = NULL, @LineId = @LineId + 1, 
                        @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0 
                end 
                select @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'') 
                if LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength+1 ) > 0 
                begin 
                    select @Line = @Line + ' ', @BlankSpaceAdded = 1 
                end 
            end 
        end 
    end 
 
 FETCH NEXT from ms_crs_syscom into @SyscomText 
end 
 
IF @Line is NOT NULL 
    INSERT #CommentText VALUES( @LineId, @Line ) 
 
select Text from #CommentText order by LineId 
 
CLOSE  ms_crs_syscom 
DEALLOCATE  ms_crs_syscom 
 
DROP TABLE  #CommentText 
 
return (0) -- sp_helptext 

Comma Seprator in SQL

create table Employee_Names (Names nvarchar(100))

insert into employee_names values ('Dinesh,Manoj,Kumar,Radha,Dhiraj,Saraswathi')
insert into employee_names values ('Judu,Anoop,Srinivasan')



Declare @person as varchar(255), @cnt smallint,@loc smallint,@prevloc smallint, @str varchar(255)

declare @Names nvarchar(max)

Declare Test_Cursor Cursor for
select names from employee_names where names like '%,%'

open Test_Cursor
Fetch NEXT from Test_Cursor INTO @Names
While @@fetch_status=0
Begin

Select @prevloc=0,@loc=1,@cnt=1,@person = @Names
While @loc != 0
Begin
    set @prevloc=(case when @loc = 1 then 0 else @loc end) +1
    set @loc = charindex(',',@person,@loc+1)
    Set @str = substring(@person,@prevloc,(Case when @loc = 0 then len(@person) - @prevloc + 1 else @loc - @prevloc end))
    print 'String = ' + @Str
--select   @Str

End  
Fetch NEXT from Test_Cursor INTO @Names

End
Close Test_Cursor
Deallocate Test_Cursor

Insert Query Generator for MS-SQL

insert statement generator

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 
 
ALTER PROC [dbo].[InsertGenerator] 
(@tableName varchar(100)) as 
 
--Declare a cursor to retrieve column specific information for the specified table 
DECLARE cursCol CURSOR FAST_FORWARD FOR  
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName 
OPEN cursCol 
DECLARE @string nvarchar(max) --for storing the first half of INSERT statement 
DECLARE @stringData nvarchar(max) --for storing the data (VALUES) related statement 
DECLARE @dataType nvarchar(max) --data types returned for respective columns 
SET @string='INSERT '+@tableName+'(' 
SET @stringData='' 
 
DECLARE @colName nvarchar(50) 
 
FETCH NEXT FROM cursCol INTO @colName,@dataType 
 
IF @@fetch_status<>0 
 begin 
 print 'Table '+@tableName+' not found, processing skipped.' 
 close curscol 
 deallocate curscol 
 return 
END 
 
WHILE @@FETCH_STATUS=0 
BEGIN 
IF @dataType in ('varchar','char','nchar','nvarchar') 
BEGIN 
 --SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+' 
 SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+' 
END 
ELSE 
if @dataType in ('text','ntext') --if the datatype is text or something else  
BEGIN 
 SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+' 
END 
ELSE 
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly 
BEGIN 
 SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+' 
END 
ELSE  
IF @dataType='datetime' 
BEGIN 
 --SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+' 
 --SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations 
 --SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+' 
 SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+' 
  --                             'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations 
END 
ELSE  
IF @dataType='image'  
BEGIN 
 SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+' 
END 
ELSE --presuming the data type is int,bit,numeric,decimal  
BEGIN 
 --SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+' 
 --SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+' 
 SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+' 
END 
 
SET @string=@string+@colName+',' 
 
FETCH NEXT FROM cursCol INTO @colName,@dataType 
END 
DECLARE @Query nvarchar(4000) 
 
SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName 
exec sp_executesql @query 
--select @query 
 
CLOSE cursCol 
DEALLOCATE cursCol 
 
 
eg:-


exec Insertgenerator '<table_name>'