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.
CREATE PROC usp_FindMyTable (@TABLE_NAME sysname = null)
SET NOCOUNT ON
TRUNCATE TABLE SearchTable -- If you are using it again.
IF @TABLE_NAME IS NULL
PRINT 'No Table to look for. Please supply a table name. Like: ' + CHAR(13)
+ ' EXEC usp_FindMyTable Orders'
DECLARE @MAX_dbname sysname, @dbname sysname, @sql varchar(8000)
SELECT @MAX_dbname = MAX([name]), @dbname = MIN([name]) FROM master..sysdatabases
WHILE @dbname @dbname
SELECT * FROM SearchTable
SET NOCOUNT OFF
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.
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
SET @RegistryPath='Software\Microsoft\Microsoft SQL Server\'+CONVERT(VARCHAR(25),SERVERPROPERTY('INSTANCENAME'))+'\MSSQLServer\SuperSocketNetLib\Tcp\'
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @RegistryPath, 'tcpPort'
Have a magical day!
This is just a quick post to say thank you for whomever put me on this list. I can’t believe it, and am totally bouncing off the walls right now. The survey results are here:
I will submit my abstracts now. I was debating on if anyone would want to listen to me, thank you for making me feel important! Keep your fingers crossed that I get accepted to speak. I have a different dress for each day of the PASS Summit, I just have to figure out how to pack them. I hope to see you all there!
Have a magical day, I totally am!
I’m certainly no Reporting Services master, and far from a Business Intelligence Developer, but I do like to tinker around with reports for the bosses from time to time
I like Excel for it’s conditional formatting feature, especially in building heat-map type charts.
So I was looking for a way to replicate the functionality in SSRS and figured I’d pass it on:
Just as an example, boss comes and asks “Hey, what’s the busiest time of day for our order-entry website?”
This is easily accomplished by changing the Properties of the cell ( in my case textbox2 ) Fill > BackgroundColor > and click on Expression…
From here it is pretty intuitive… I select a color from the values field and using the SWITCH function can apply green for < 100, yellow for 100 – 500, red for 500-1000, etc.
I end up using the more colors link down right to select a visually appealing color range.
This technique can also be used to create alternating background colors that makes a report more readable as used here…
It’s not magic, but logic. However, as Andrea says “Have a magical day!”
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'
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 (
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!
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)
Greeting and Other Salutations!
I have been excessively swamped trying to get some speaking topics ready for PASS (please oh please oh please pick me) and Utah Code Camp (http://utahgeekevents.com/) which I am also hoping to speak at next month. But I have amazing news. I met Jimmy May! I felt like a teenager when I met him. He walked up and said “Hi, I’m Jimmy. ” I screamed back (and sorry to scare you Jimmy) “Jimmy May?!!!!” Yeah, it was awesome. He is brilliant and so nice! So what did I learn? Some really cool stuff about columnstore indexing. Do you want to learn it too? Check out Jimmy’s blog at http://blogs.msdn.com/b/jimmymay/ . He has also promised to post the video of me freaking out when I met him so it should be really fun.
My point to all of this, you can be great too. Jimmy has worked hard to get to where he is. He asked the questions, did the research and has the passion to continue to grow. We all run into weird stuff (post soon coming about Agent Signing Certificates) that we don’t understand. Take a few minutes, learn it and file it into your beautiful brain. Find your passion and be amazing. I love this pep talk (http://www.youtube.com/watch?v=l-gQLqv9f4o) and have watched it multiple times in the last few weeks to keep me going. Take his advice, dance and be amazing. Save a dance for me too, princesses love to dance.