Category Archives: Awesome T-SQL

by

They said “If you don’t let it out, You’re going to let it eat you away” – CHARINDEX with SUBSTRING

Greetings and other Awesome Salutations,
It has been a while because I am learning oodles of new stuff. There are a few things that I don’t want to forget so here goes.

CHARINDEX is a super easy way to get the starting location of something within a string. For example, I was looking for the last half of Superhero names. My column is SuperheroName in the Comics Table and pretend there are dashes in their names, please just for this example – I know there aren’t really dashes please continue to love me with my flaws.
So…
SELECT TOP 4 SuperheroName
FROM Comics

Results:
Bat-man
Bat-girl
Super-man
Super-girl

Now the magic, CHARINDEX will give you the location of the character you specify.

SELECT TOP 4 CHARINDEX('-', SuperheroName)
FROM Comics

Results:
4
4
6
6

How cool is that?!! But I want to pull back everything after the “-” character, which means I have to get tricky because CHARINDEX will only give me the location number of where it is within the string. SUBSTRING will save the day (Tada!). SUBSTRING is really cool. I am going to pass it ColumnName, Starting Position (using CHARINDEX), Ending Position (using LEN and the column name that will get me the length of that column on that row). So it looks like this:

SELECT TOP 4 SUBSTRING(SuperheroName,CHARINDEX('-',SuperheroName), LEN(SuperheroName))
FROM Comics

Results:
-man
-girl
-man
-girl

But that still isn’t quite what I want…so I am going to add 1 to the Starting point position number (this will allow me to skip over the character I am using):

SELECT TOP 4 SUBSTRING(SuperheroName,CHARINDEX('-',SuperheroName)+1, LEN(SuperheroName))
FROM Comics

Results:
man
girl
man
girl

Oh but wait, there is more! I think it is really silly to have the dashes in the first place so check this out:
SELECT TOP 4
SUBSTRING(SuperheroName,1,CHARINDEX('-',SuperheroName)-1) + SUBSTRING(SuperheroName,CHARINDEX('-',SuperheroName)+1, LEN(SuperheroName))
FROM Comics

Results:
Batman
Batgirl
Superman
Supergirl

Holy Rusted Metal Batman! What did we do? This time in my substring I used the first position and then the CHARINDEX -1 to tell me what was right before the dash. Then I combined them! With these super powers combined…well awesome things happen.

Enjoy!

by

You’ve been Thunderstruck and can now Find any Table in an Instance

Have you ever remembered what Intstance a table was on but not the database name? This will solve your problem. First you will create the table, then the procedure that will look for it and finally, you will execute the procedure with your table name. (At the end you can drop it so no one knows your secret.)
It returns the database, the schema, the table name and a type.

SET NOCOUNT ON
CREATE TABLE SearchTable(TABLE_CATALOG sysname, TABLE_SCHEMA sysname, TABLE_NAME sysname, TABLE_TYPE varchar(50)) --If you haven't ever used this.
GO 
 
CREATE PROC usp_FindMyTable (@TABLE_NAME sysname = null)
AS
SET NOCOUNT ON 

TRUNCATE TABLE SearchTable -- If you are using it again.
IF @TABLE_NAME IS NULL
BEGIN
PRINT 'No Table to look for. Please supply a table name. Like: ' + CHAR(13)
+ ' EXEC usp_FindMyTable Orders'
GOTO usp_FindMyTable_Exit
END 
DECLARE @MAX_dbname sysname, @dbname sysname, @sql varchar(8000) 
SELECT @MAX_dbname = MAX([name]), @dbname = MIN([name]) FROM master..sysdatabases 
WHILE @dbname @dbname
END 

SELECT * FROM SearchTable 
usp_FindMyTable_Exit:
SET NOCOUNT OFF
RETURNGO
 
EXEC usp_FindMyTable 'Unicorn' -- put in the table you are searching for here. GO
 
SET NOCOUNT OFF
DROP PROC usp_FindMyTable --Clean up so no one knows
DROP TABLE SearchTable --Really, it is our secret. More clean up.
GO

by

Don’t you cry no more…you can find the TCP Port

Greetings to the Kingdom!

Huge thanks to my friends Dan and Tamie for this one.  It will help you determine the TCP Port for an instance.  I love my friends in the SQL Community and am so happy they are willing to share!

DECLARE @key VARCHAR(50), @RegistryPath VARCHAR(200)
IF (SERVERPROPERTY('INSTANCENAME')) IS NULL
BEGIN
SET @RegistryPath='Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\'
END
ELSE
BEGIN
SET @RegistryPath='Software\Microsoft\Microsoft SQL Server\'+CONVERT(VARCHAR(25),SERVERPROPERTY('INSTANCENAME'))+'\MSSQLServer\SuperSocketNetLib\Tcp\'
END
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @RegistryPath, 'tcpPort'

Have a magical day!

by

Let Me Riddle You a Ditty It’s Just an Itty Bitty Little Thing on My Mind About a Group of Unused Indexes Being Disabled All at the Same Time…

Now the funny thing about it, not a story without it, but the story is mine and it ended just fine…Happily Ever After

Today I have a Vendor Application that has WAY too many indexes and they need to rebuild them all tonight in a very tight window.  I am disabling all the unused indexes to allow for a faster rebuild and then will rebuild the disabled indexes tomorrow night after all the heavy work is done.  The Vendor is nervous about us disabling indexes that haven’t been used in over a month.  The good news is that you can simply disable them and then rebuild them if they are needed.  I know the rebuild will take some time, but we have all agreed that if they haven’t been used in over a month, they most likely won’t be used in the next 24 hours.  Now the good part. Running this t-sql code not only tells you what indexes have not been used since the last reboot, but it also gives you a rebuild indexes script that can easily be changed to a disable script. The scripts can all be run at once and will save you oodles of time.

SELECT OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName,
OBJECT_NAME(I.OBJECT_ID) AS ObjectName,
I.NAME AS IndexName,
'ALTER INDEX [' + I.Name + '] ON ' + OBJECT_NAME(I.OBJECT_ID) + ' ReBuild' --' Disable'
AS SQLToRebuildOrDisable
FROM sys.indexes I
WHERE /* only get indexes for user created tables*/
OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1
/* making sure it is not a primary key*/
and i.is_primary_key = 0
and i.is_unique = 0
/* If the are returned in this table, they are in use so we are getting everything not in this table.*/
AND NOT EXISTS (
SELECT index_id
FROM sys.dm_db_index_usage_stats
WHERE OBJECT_ID = I.OBJECT_ID
AND I.index_id = index_id
-- This will limit to the db you are on.
AND database_id = DB_ID())
-- The Index must have a name for this to work.
AND I.name IS NOT NULL
ORDER BY SchemaName, ObjectName, IndexName

I hope it helps you as much as it has helped me today!
Have a magical day!

by

Say what you mean, tell me I’m right and reset the identity….

Today I wanted to change the number in my identity column to be way higher. My plan is to union my new table with another table that has a low id and be able to tell what is coming from where.  I know that my old table will never got over 100,000 records, but to be safe I set the number in the new table to start at 700,000.  This is called seeding the identity.  You can also reset your identity this way.  Use caution when you are doing this because if you reseed the identity and it is lower than an existing identity in the table, it will complain when you hit that identity if you are inserting.   Here is my example:

DBCC CHECKIDENT ([Schema.TableName], RESEED, 700000)

Happy Planting!

by

You must not know about me, I can kill connections in a minute

It is still snowy and cold outside so short post today about killing connections.  I love this quick little script and can’t remember where I got it from, so huge Thank You to the person that created it.  I use it when I need to restore a database that still has active connections.  This will first print a list of the connections it wants to kill for the database in the where clause, if you uncomment the “EXEC” part, you will get the fastest connection killer you have ever used. 

DECLARE @SQL VARCHAR(8000)

SELECT @SQL=COALESCE(@SQL,'')+'Kill '+CAST(spid AS VARCHAR(10))+ '; '
FROM sys.sysprocesses
WHERE DBID=DB_ID('DatabaseName')

PRINT @SQL
--EXEC(@SQL) /*Replace the print statement with exec to execute*/

Enjoy and Have a Magical Day!

by

Take a good hard look, I’m not okay – I created a cursor!

Last week I did something I promised myself I would never do again. It was horrible, dirty and made me feel like the worst DBA in the world. I didn’t drop a production database or truncate the most important table, this was way worse. I created a cursor. I am hoping that can I appeal to your kindness and understanding. Please help me forgive myself so the healing may begin.

It all started when I needed to build an Error Log report in SSRS. We are using a PowerShell script to go out to all our servers and bring a copy of the latest SQL error log to a centralized database and table. I was making a call to this table and only pulling back the last 4 days worth of data. I have learned over the years that with SSRS if you try to feed it too much data, you don’t get a report, you get a big red “X”. I was getting that “X”. Since I wasn’t allowed to modify the base table incase we needed to review it for other troubleshooting matters, I created a new table that just had the last 4 days worth of data. I was still getting the red “x”. I knew I had to filter my data more. We have a table of specific errors that we want to show up on this report. But they are not the complete error, just parts of it so the error remains generic enough to pull it from any server. In order for me to compare these fields, I need to use a “LIKE” clause. I tried joining with the “LIKE” clause, but I didn’t have any luck. I even tried a “Cross Join”  and a “Hard Join” but it still didn’t work.

In my despair, I was talking to a friend of the kingdom and he suggested a cursor. I backed away in shock. “No, no! Cursors are bad!” I cried. He calmly looked at me and said, “You are trying to use a sledge-hammer to hang a picture right now, try the cursor.”

I sat back down, arguing with myself. Could I do this? Betray what I have always believed and actually use a cursor? I figured I would at least write one and hope that I came up with a better solution in the process.

I know you are all excited to see the monster that I have created, truly a Royal Pain:

SET ANSI_PADDING ON
GO

Declare @objid int
Declare @ErrorMsg Varchar (255)

DECLARE SQLErrorLog_Cursor CURSOR FOR

/*Here is where I load my table of filters. This will allow me to add more errors to the report later without rebuilding anything.*/

SELECT [objid],[ErrorMsg]
FROM [LightsOn].[dbapp].[SQLErrorLogParseList]
OPEN SQLErrorLog_Cursor

FETCH NEXT FROM SQLErrorLog_Cursor
INTO @objid, @ErrorMsg
WHILE @@FETCH_STATUS = 0
BEGIN

/*This is my main query that I need to apply the filter to. InstanceSQLLog is the really large table I am filtering down.*/
INSERT INTO dbapp.SQLErrorLogFilter
SELECT InstanceName, LogDate, Text, P.FullName AS PrimaryOps, P.ID AS PrimaryOpsID,P3.FullName AS SecondaryOps, P3.ID AS SecondaryOpsID
FROM dbapp.InstanceSQLLog ISL
LEFT JOIN dbapp.Instance I ON ISL.InstanceId = I.InstanceID
LEFT JOIN People P ON I.PrimaryOpsDBAId = P.Id
LEFT JOIN dbo.People P3 ON I.SecondaryOpsDBAId = P3.ID
WHERE LogDate > GETDATE()-4 AND TEXT LIKE '%' + @ErrorMsg + '%'

FETCH NEXT FROM SQLErrorLog_Cursor
INTO @objid, @ErrorMsg
END

CLOSE SQLErrorLog_Cursor
DEALLOCATE SQLErrorLog_Cursor
GO

This is one of the steps of a 4 part job that I auto run early in the morning so the reports are in the specified DBA’s Email when they arrive at work (each report is customized to the DBA and the servers that are their Primary responsiblity.).

The whole process goes like this: I truncate the dbapp.SQLErrorLogFilter Table, run this cursor, Truncate the final grouped and filtered table for the report and repopulate that so SSRS gets a very clean and easy to read table. The whole process takes about 8 minutes. I am still trying to find a better way, but this works for now and the team is happy.

I have been toying with the idea of adding FastForward to the cursor. Does anyone have any opinions of why I should or should not do that?

Thank you for being so kind to read. I love how included the community makes me feel and how much everyone is willing to teach each other.

Have a magical week!

Andrea

by

One script to rule them all, one script to find them – By Scott Ashby

Well one script to find them anyway…  I wanted to share a TSQL script that I use ALL THE TIME to quickly and easily find specific text in the names or code of all objects in a database.  This script has countless uses, but what I use it for most often is to track down certain tables, views, or stored procedures, that are referenced in other objects. 

For example, If I have been making updates to a particular view and I’m pretty sure those updates are going to break a number of views, stored procedures, etc, I will enter the name of the view I am updating as the parameter.  This script will return any object that contains that text so I can review each and make any additional needed changes to ensure reports or applications continue to run smoothly.

The script looks for text in all names of objects as well as looking at the code for objects such as views, stored procs, functions, triggers, etc.  Pretty slick.  I hope you find it helpful. 

USE DBName

DECLARE@SearchText VARCHAR(50)

SET@SearchText = '%EnterTextHere%'

--In Script

SELECT DISTINCT OBJECT_NAME(id) AS ObjectName,type_desc AS ObjectType,'In Script' ASCategory

FROM SYSCOMMENTS S

INNER JOINSYS.OBJECTS O ON O.Object_Id = S.id

WHERE S.text LIKE @SearchText

UNION

--In Object Name

SELECT Name AS ObjectName,type_desc AS ObjectType,'In Object Name' ASCategory

FROM sys.all_objects

WHERE name LIKE @SearchText

UNION

--Table Columns

SELECT O.name AS ObjectName, O.Type_Desc AS ObjectType, 'In Column: ' + C.name AS Category

FROM sys.all_objects O

INNER JOINsys.all_columns C ON O.object_id = C.object_id

WHERE C.name LIKE@SearchText

AND O.Type_Desc= 'USER_TABLE'

ORDER BY Category DESC, ObjectType,ObjectName