Search Text from Stored Procedure Definition
Posted: September 29, 2014 Filed under: Uncategorized Leave a commentYou 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')
Bulk Insert txt Files (templated files) into SQL Server Table and Add File Name Column with Powershell
Posted: September 29, 2014 Filed under: Uncategorized Leave a comment
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
Posted: September 26, 2014 Filed under: Uncategorized Leave a commentAlter 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
}
Bulk Insert Files From Template With File Name –T-SQL
Posted: September 26, 2014 Filed under: Uncategorized Leave a commentAlter 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
Add Space In Front of A Capital Letter Within Connected Words
Posted: September 25, 2014 Filed under: Uncategorized Leave a comment
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
Posted: September 25, 2014 Filed under: Uncategorized Leave a comment
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)
Reorder Column List for a Destination Flat File in SSIS
Posted: September 25, 2014 Filed under: Uncategorized Leave a commentThere 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.
Using STUFF FUNCTION to Remove Some Characters with T-SQL
Posted: September 25, 2014 Filed under: Uncategorized Leave a comment
CREATE TABLE table1 (COLOMN1 VARCHAR(50))
INSERT INTO table1
VALUES ('abcd'),
('abcd'),
('ab'),
(NULL),
('')
SELECT
/*
--Msg 537, Level 16, State 2, Line 62
--Invalid length parameter passed to the LEFT or SUBSTRING function.
LEFT(COLOMN1,Len(COLOMN1) - 3)
*/
COALESCE(Stuff(COLOMN1, Len(COLOMN1) - 3, 3, ''), COLOMN1) as COLOMN1
FROM TABLE1
DROP TABLE TABLE1
Find Account Number with Negative Balance for Consecutive 5 Days
Posted: September 25, 2014 Filed under: Uncategorized Leave a comment
create table #p(cal date, ac# int, bal int)
insert into #p values ( GETDATE(), 1, -1),
( GETDATE(), 2, 1),
( GETDATE(), 3, 1),
( GETDATE(), 4, -2),
( GETDATE(), 5, -6),
( GETDATE()-1, 1, -1),
( GETDATE()-1, 2, 1),
( GETDATE()-1, 3, 1),
( GETDATE()-1, 4, 2),
( GETDATE()-1, 5, -7),
( GETDATE()-2, 1, -2),
( GETDATE()-2, 2, -1),
( GETDATE()-2, 3, -1),
( GETDATE()-2, 4, 2),
( GETDATE()-2, 5, -6),
( GETDATE()-3, 1, -2),
( GETDATE()-3, 2, -1),
( GETDATE()-3, 3, -1),
( GETDATE()-3, 4, 2),
( GETDATE()-3, 5, -2),
( GETDATE()-4, 1, -4),
( GETDATE()-4, 2, 1),
( GETDATE()-4, 3, 1),
( GETDATE()-4, 4, -2),
( GETDATE()-4, 5, -2),
( GETDATE()-5, 1, -4),
( GETDATE()-5, 2, 1),
( GETDATE()-5, 3, 1),
( GETDATE()-5, 4, 2),
( GETDATE()-5, 5, -1),
( GETDATE()-6, 5, -1)
--update #p
-- set bal=-1*bal
-- where cal='2014-09-22' and [ac#]=5
;with mycte as ( Select *
,row_number() Over(Order by [ac#], cal) - row_number() Over(Partition by [ac#], sign(bal) Order by [ac#], cal) rnDelta
from #p)
,mycte1 as (
select * ,
sum(Case when bal< 0 Then 1 Else 0 End) Over(Partition by [ac#], rnDelta Order by [ac#],cal ) cnt
from mycte
)
SELECT distinct [ac#] from mycte1
Where cnt>=5
drop table #p
Delete Data from a Large Table in Batch
Posted: September 24, 2014 Filed under: Uncategorized Leave a commentSometimes, you may have a need to clean up some tables to remove obsolete data. If the row size is too big, you may run into a long running process.
You can cut the operation in small batches to run. Here is an example:
declare @Chunk int=500, @ToBeDeleted int = 1
WHILE @ToBeDeleted > 0
BEGIN
BEGIN TRAN
DELETE TOP (@Chunk) FROM [dbo].[accountlog]
WHERE createdate < Dateadd(day, -180, Getdate())
SET @ToBeDeleted = @@ROWCOUNT
PRINT 'Deleted rows: ' + Cast(@ToBeDeleted AS VARCHAR(10))
COMMIT TRAN
END
Jingyang Li 