Search Text from Stored Procedure Definition

You can search text from stored procedure with the following code quickly:



SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%mySearch%' 
    AND ROUTINE_TYPE='PROCEDURE'

--Or
SELECT  [object_id] ,
        [name] ,
        [text]
FROM    sys.[syscomments] AS sc
        INNER JOIN sys.[procedures] AS pr 
	   ON sc.[id] = pr.[object_id]
WHERE   sc.[text] LIKE '%myprocedure%'

 

But you may not find the search word from some procedures you are pretty sure that they are there. Because the Routine_Definition and text from syscomments are nvarchar(4000) in length and the column contains the truncated version of the SP definition.

Arron Bertrand has posting to provide multiple ways to get back the full definition of stored procedures.
Here is the one I grab from his posting and it works pretty good:




	SELECT [schema] = OBJECT_SCHEMA_NAME(o.[object_id]), o.name
    FROM sys.objects AS o
    INNER JOIN sys.sql_modules AS m
    ON o.[object_id] = m.[object_id]
    WHERE m.[definition] LIKE '%mySearch%'
    AND o.[type] IN ('P')


 

The link:
http://sqlblog.com/blogs/aaron_bertrand/archive/2011/11/08/t-sql-tuesday-24-dude-where-s-the-rest-of-my-procedure.aspx

Advertisements

Bulk Insert txt Files (templated files) into SQL Server Table and Add File Name Column with Powershell




Import-Module -Name 'SQLPS' -DisableNameChecking   
 
$workdir="C:\temp\test\"
$svrname = "MC047012\MSSQL2014" 
 
 Try
{
 
#Change default timeout time from 600 to unlimited
$svr =  new-object ('Microsoft.SqlServer.Management.Smo.Server') $svrname 
$svr.ConnectionContext.StatementTimeout = 0
$table="test1.dbo.myRegions"
 
$q1 = @"
Use test1;
IF COL_LENGTH('dbo.myRegions','filename') IS NOT NULL
BEGIN
ALTER TABLE test1.dbo.myRegions DROP COLUMN filename;
END
"@
#add the filename column to the target table
Invoke-Sqlcmd -ServerInstance $svr.Name  -Database  master -Query  $q1

$dt = (get-date).ToString("yyyMMdd") 
$formatfilename="$($table)_$($dt).xml"
$destination_formatfilename ="$($workdir)$($formatfilename)"
 
$cmdformatfile="bcp $table format nul -c -x -f $($destination_formatfilename) -T  -t\t  -S $($svrname) "
Invoke-Expression $cmdformatfile
#Delay 1 second
Start-Sleep -s 1


$q2 = @"
Alter table test1.dbo.myRegions  Add filename varchar(500) Null;
"@
#add the filename column to the target table
Invoke-Sqlcmd -ServerInstance $svr.Name  -Database  master -Query  $q2 



$files = Get-ChildItem $workdir
$items = $files | Where-Object {$_.Extension -eq ".txt"} 
  
for ($i=0; $i -lt $items.Count; $i++) {
 
$strFileName = $items[$i].Name
$strFileNameNoExtension= $items[$i].BaseName
 
$query = @"
BULK INSERT test1.dbo.myRegions from '$($workdir)$($strFileName)' WITH (FIELDTERMINATOR = '\t', FIRSTROW = 2, FORMATFILE =  '$($destination_formatfilename)');
"@ 
 
Invoke-Sqlcmd -ServerInstance $svr.Name  -Database master  -Query  $query  -querytimeout 65534

# Update the filename column
Invoke-Sqlcmd -ServerInstance $svr.Name  -Database master  -querytimeout 65534  -Query  "Update test1.dbo.myRegions SET filename= '$($strFileName)' WHERE filename is null; "

# Move uploaded file to archive
If ((Test-Path "$($workdir)$($strFileName)") -eq $True) { Move-Item -Path  "$($workdir)$($strFileName)" -Destination  "$($workdir)Processed\$($strFileNameNoExtension)_$($dt).txt"}

 }
  }
Catch [Exception]
 {
 write-host "--$strFileName "$_.Exception.Message
 }
 

Bulk Insert Files From Template With File Name –Powershell



Alter table  [dbo].[myRegions]
drop column filename  


 --1Generate XML format file 
 DECLARE @sql VARCHAR(4000) 
SET @sql='bcp test1.dbo.myRegions format nul -c -x -f  "C:\temp\test\myFormatmyRegions.xml" -T -t\t -S'+ @@servername
exec master..xp_cmdshell @sql

--Add a filename column to the target table
Alter table  [dbo].[myRegions]
Add filename varchar(200) null

 

 --    -t"|" for pipe delimited
 --   -t\t for tab delimited
 --   -t, for comma delimited


 



Import-Module -Name 'SQLPS' -DisableNameChecking  


$workdir="C:\temp\test\"
$svrname = "MC047012\MSSQL2014"


Try
{

#Change default timeout time from 600 to unlimited
$svr =  new-object ('Microsoft.SqlServer.Management.Smo.Server') $svrname 
$svr.ConnectionContext.StatementTimeout = 0

 

$files = Get-ChildItem $workdir
 
for ($i=0; $i -lt $files.Count; $i++) {
$dt = (get-date).AddDays(-$i).ToString("yyyMMdd") 
$strFileName = "raw_US_$($dt).txt"
If (Test-Path "C:\temp\test\$strFileName"){

$query = @" 
BULK INSERT test1.dbo.myRegions from '$($workdir)$($strFileName)' WITH (FIELDTERMINATOR = '\t', FIRSTROW = 2, FORMATFILE = 'C:\temp\test\myFormatmyRegions.xml');
Update test1.dbo.myRegions SET filename= '$($strFileName)' WHERE filename is null;

"@


Invoke-Sqlcmd -ServerInstance $svr.Name  -Database master  -Query  $query 

}
}
}

Catch [Exception]
 {
 write-host "--$strFileName "$_.Exception.Message
 }





 

http://social.msdn.microsoft.com/Forums/en-US/3d195bb5-89b0-4ec4-bf50-bcd9b2ca9662/can-i-use-bulk-import-to-add-fields-to-a-table?forum=transactsql


Bulk Insert Files From Template With File Name –T-SQL



Alter table  [dbo].[myRegions]
drop column filename  


 --1Generate XML format file 
 DECLARE @sql VARCHAR(4000) 
SET @sql='bcp test1.dbo.myRegions format nul -c -x -f  "C:\temp\test\myFormatmyRegions.xml" -T -t\t -S'+ @@servername
exec master..xp_cmdshell @sql

--Add a filename column to the target table
Alter table  [dbo].[myRegions]
Add filename varchar(200) null

 

 --    -t"|" for pipe delimited
 --   -t\t for tab delimited
 --   -t, for comma delimited



DECLARE @intFlag INT
SET @intFlag =1

WHILE (@intFlag <=3)
BEGIN
 
PRINT @intFlag
 
declare @fullpath1 varchar(1000)
select @fullpath1 = '''C:\temp\test\raw_US_' + convert(varchar(8), getdate()-@intFlag , 112) + '.txt'''
declare @cmd1 nvarchar(1000)
select @cmd1 = 'BULK INSERT test1.dbo.myRegions from ' + @fullpath1 + ' with (FIELDTERMINATOR = ''\t'', FIRSTROW = 2, FORMATFILE = ''C:\temp\test\myFormatmyRegions.xml'') ;
Update test1.dbo.myRegions SET filename=' + @fullpath1 + ' WHERE filename is null;'

print @cmd1
exec (@cmd1)
 
SET @intFlag = @intFlag + 1
   
END


 

select count(*) FROM test1.dbo.myRegions   

where filename Like '%raw_US_20140925.txt'

 

--  truncate table test1.dbo.myRegions

 

http://social.msdn.microsoft.com/Forums/en-US/3d195bb5-89b0-4ec4-bf50-bcd9b2ca9662/can-i-use-bulk-import-to-add-fields-to-a-table?forum=transactsql


Add Space In Front of A Capital Letter Within Connected Words


create table test (reports varchar(50))

Insert into test values('admissionPage'),('topHighCost'),('requestedReportsZu')
;with mycte as (
Select reports, substring(reports,d.num,1) s 
,Case when ASCII(SUBSTRING(reports,d.num,1)) between 65 and 90  Then 1 else 0 End IsCapital
from test
cross apply(values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)) d(num)
)

,mycte1 as (
SELECT Distinct reports = (
SELECT   Case When IsCapital=1 Then ' '+s Else s End
 FROM mycte m
 WHERE m.reports=m1.reports
 FOR XML PATH(''))
 FROM mycte m1)


Select  reports from mycte1

drop table test

 

Take the Whole Number (No rounding up ) T-sql



declare @val as decimal(6,3)
 
set @val = 20.575
 
 
 
select  @val
, Cast(round(@val,-1) as int) 
, parsename(cast(@val as varchar(20)),2)
,stuff(cast(@val as varchar(20)),charindex('.',cast(@val as varchar(20))),len(cast(@val as varchar(20))),'')
,floor(@val)
,cast(@val as int) myInt,
  Replace(@val,Replace(ABS(@val) % 1,'0.','.'),'')
 
,Cast (N'<root><row>' + Replace(@val, '.', '</row><row>') + '</row></root>' AS XML).value('(/root/row)[1]', 'nvarchar(50)')
 
, Stuff(@val, Charindex(0X2E, Cast(Ltrim(@val) AS VARBINARY(50)), 1), len(@val), '')  

,Cast(@val-ABS(@val) % 1 as Int)


 

http://blogs.msdn.com/b/samlester/archive/2012/10/23/tsql-solve-it-your-way-parsing-tips-and-tricks-returning-digits-after-the-decimal-point.aspx


Reorder Column List for a Destination Flat File in SSIS

There is a much easier way! Just open the dtsx-file in you favourite xml-editor and find
the DTS:FlatFileColumn-tag corresponding your FlatFileConnection. Just reorder them there and open your file again in Visual Studio.

I have a need to order a flat file from a query result in SSIS.
One option is to create a new destination flat file with the column aligned to the requirement.
Another option is to modify the dtsx file directly if you dare enough. The dtsx file is special XML with all information about your package.
Within Visual STidio, right click on your package name —- view code —-(code in XML format is open)

Find the code block under your destination connection manager:
<DTS:ConnectionManager


and you can resort your column sequence and save the file.
You should be ready to go without any other work. You are done.