TSQL – Getting every day of the Current Month

This came handy, when i wanted to get all dates of the current month when joining with mutiple tables… and not all the tables  had all dates of the month

;WITH
   CTE0 AS(SELECT 1 AS c UNION ALL SELECT 1),
    CTE1 AS(SELECT 1 AS c FROM CTE0 AS A, CTE0 AS B),
CTE2 AS(SELECT 1 AS c FROM CTE1 AS A, CTE1 AS B),
CTE3 AS(SELECT 1 AS c FROM CTE2 AS A, CTE2 AS B),
  SeqNumbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS SeqNumber FROM CTE3)

SELECT SeqNumber,CONVERT(VARCHAR(12),(GETDATE()-(DAY(GETDATE())-SeqNumber)),101) AS ReportingDate
FROM
    SeqNumbers
WHERE SeqNumber <=    DAY(GETDATE())

Searching through Stored procedure code using TSQL script

Even if you have a refactoring tool, sometimes i wonder what are all the storedprocedures that uses this table that starts with ‘products_’  or ‘system_’… or all the tables that have a column name ‘status_id’….

so here is the script that scans all the stored procedure code, and check if the table name is found

Note  —this will also return if the table name is in the comment too.. so the logic is, even if you want to search any text (comment) in a stored procedure

 

 

1. ———————————————————————————————–
2. – Parameters
3. ———————————————————————————————–

DECLARE @tableNameLike VARCHAR(100), @columnNameLike VARCHAR(100)

– Filter only stored procedures that uses that table(s) and (optionaly) if it has a column

SET @tableNameLike = ‘enterYourTableName%’ — If you have organized all your tables with

— standard prefix, will be useful to filter

SET @columnNameLike = ‘enterYourColumnName%’ — Only if the table has this column

———————————————————————————————–

– Local Variables

———————————————————————————————–

DECLARE @spname VARCHAR(100),@TABLEname VARCHAR(100)

DECLARE @spnames TABLE(spname VARCHAR(100))

DECLARE @EXISTS INT

DECLARE @sp_start INT, @sp_END INT ,@t_start INT , @t_END INT,@sp_INdex INT , @t_INdex INT

———————————————————————————————–

– DROP before Creating Temporary Tables

———————————————————————————————–

IF EXISTS (

SELECT * FROM tempdb.dbo.sysobjects o

WHERE o.xtype IN (‘U’)

AND o.id = object_id(N‘tempdb..#spnames’)

)

DROP TABLE #spnames

IF EXISTS (

SELECT * FROM tempdb.dbo.sysobjects o

WHERE o.xtype IN (‘U’)

AND o.id = object_id(N‘tempdb..#TABLEnames’)

)

DROP TABLE #TABLEnames

– Get all the Stored procedure names

SELECT row_number() OVER(ORDER BY name) AS rownum,name

INTO #spnames

FROM sys.objects

WHERE type = ‘P’

– Get all the table names that passes the filter parameter

SELECT row_number() OVER(ORDER BY name) AS rownum,name

INTO #TABLEnames

FROM

sysobjects T

JOIN

INformation_schema.columns C

on

C.TABLE_NAME = T.name

WHERE T.name like @tableNameLike AND T.xtype = ‘U’

AND C.column_name like @columnNameLike

– SELECT * FROM #spnames

– SELECT * FROM #TABLEnames

———————————————————————————————–

– Get and Set Loop Index

———————————————————————————————–

SELECT @sp_start = 1,@sp_END = max(rownum)+1

FROM #spnames

SELECT @t_start =1,@t_END = max(rownum)+1 FROM #TABLEnames

– SELECT @sp_Start,@sp_END,@t_start,@t_END

———————————————————————————————–

– Loop through each stored procedure name

———————————————————————————————–

while @sp_start < @sp_END

BEGIN

– Get the stored procedure name

SELECT @spname = [name]

FROM

#spnames

WHERE rownum = @sp_start

– Loop through each tablename to check if it is used in the stored procedure code

while @t_start < @t_END

BEGIN

– Get the table name

SELECT @TABLEname = [name]

FROM

#TABLEnames

WHERE

rownum = @t_start

– Check if the table name exists in the stored procedure code

– I am using the SUM(CHARINDEX(xxx)) since the stored procedure code may span

– multiple rows

SELECT @EXISTS = SUM(CHARINDEX(@TABLEname,[text]))

FROM syscomments

WHERE id = object_id(@spname)

– If it is greater than zero then it is used in the stored procedure code

IF @EXISTS > 0

BEGIN

INSERT INTO @spnames

SELECT @spname

END

SET @t_start = @t_start+1 – Increment the table index

END

SET @sp_start = @sp_start+1 – Increment the storedprocedure index

SET @t_start = 1 – Reset the table index

END

———————————————————————————————–

– To list all the spnames (remember there will be duplicates)

———————————————————————————————–

SELECT DISTINCT * FROM @spnames

———————————————————————————————–

– Drop the temporary tables

———————————————————————————————–

IF EXISTS (

SELECT * FROM tempdb.dbo.sysobjects o

WHERE o.xtype IN (‘U’)

AND o.id = object_id(N‘tempdb..#spnames’)

)

DROP TABLE #spnames

IF EXISTS (

SELECT * FROM tempdb.dbo.sysobjects o

WHERE o.xtype IN (‘U’)

AND o.id = object_id(N‘tempdb..#TABLEnames’)

)

DROP TABLE #TABLEnames

———————————————————————————————–

– END

———————————————————————————————–

IceRocket Tags: TSQL Stored procedure SQL Server 2005/2008 programming

TSQL String Range Comparison with wildcard

A simple note, and couldn’t find straight answer when i googled!!. so here it is..

This is to get result from a table when filtering in string ranges that will have a wild card for ex: ‘*’

declare @sv varchar(17),@ev varchar(17)
declare @sindex int, @eindex int

set @sv = ‘ABCDE11A09′
set @ev = ‘ABCDE11A79′

– Check the * pos if exists,if doesn’t exists return the len +1 , assuming the * at the last
select @sindex = case when charindex(‘*’,@sv)> 0 then charindex(‘*’,@sv) else len(@sv)+1 end
select @eindex = case when charindex(‘*’,@ev)> 0 then charindex(‘*’,@ev) else len(@ev)+1 end

– remove the * from the range strings
select @sv = case when charindex(‘*’,@sv)> 0 then substring(@sv,0,@sindex) else @sv  end
select @ev = case when charindex(‘*’,@ev)> 0 then substring(@ev,0,@eindex) else @ev  end

select @sindex,@eindex,@sv,@ev
select mycolumn,substring(VIN,0,@sindex), substring(VIN,0,@eindex) from mytable
where
    substring(mycolumn,0,@sindex) >= @sv AND
    substring(mycolumn,0,@eindex) <= @ev
ORDER BY mycolumn

IceRocket Tags: TSQL,SQL SERVER,Programming

SQL Express 2005 – A Basic ETL Trick

For anyone using the SQL Express 2005, and going over or got over the frustration of not having the DTS… and more over not  able to use SSIS as some type of runtime that you distribute with your packaged software application…

Well, i thought i would share what i did, it is not rocket science, but works to extract the data without writing another module to maintain, rather using only command line or a batch file using sqlcmd.

If you haven’t explored sqlcmd, please do so, just go to the command line, and type sqlcmd/?, you can all kinds of options, and particularly we can look at the options that will let you execute the sql directly or better, you can pass a file with set of sql or stored procedure to execute.

I could have done this creating a stored procedure, but i didn’t want to modify the 3rd party application’s database, so i wrote a  getxmldata.sql  which outputs a results in xml (you can find everywhere how to output data in xml format using tsql)

then i wrote a batch file getxmldata.bat

sqlcmd -Slocalhost\MyDatabaseInstanceName -E -d MyDatabaseName -i getxmldata.sql -o output.xml

Something more to explore..

You can pass parameters to the your batch file and pass it to your sql file .. to make it more dynamic..

and if you want to schedule this bat file to run, frequently say every one hour or every 15 minutes..  you can use the windows scheduler to run this batch file..

you know where to find the windows tasks scheduler? it has a nice wizard to do what you want to do… if you can’t find it.. ask me…

 

IceRocket Tags: SSIS,ETL,SQL Express 2005,schedule tasks