August 24, 2015 at 9:07 am
Wow some great lists. This might be a good thread to compile into an article.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 24, 2015 at 10:58 am
Steve Jones - SSC Editor (8/24/2015)
Wow some great lists. This might be a good thread to compile into an article.
Ha! That's the plan, but I'm wondering now if I should two articles. The one I had in mind and a second that includes the scripts I'm not going to include in the first one.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 25, 2015 at 1:22 am
Bit late to the party, but here you go :-
CREATE / ALTER VIEW / PROCEDURE
sp_who2 ACTIVE
DBCC SQLPERF (LOGSPACE)
RESTORE DATABASE . . . . .
UPDATE table . .
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
August 25, 2015 at 5:44 am
1. dateadd(day, 0, datediff(day, 0, getdate()))
2. Create a new database from a backup file. Give it a new name and change the physical and logical filenames as well.
3. Find all permissions (server and all databases) granted to a specific login
4. Show me the worst-performing queries recently
5. set statistics time, io on
For #1, I use a lot of date math for whole days, weeks, etc. My reference page is http://qa.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/ and I even have a procedure I can call to display them for me so I don't have to switch windows.
August 25, 2015 at 6:31 am
Here are my five:
1) dbcc checkdb with DATA_PURITY
2) I reckon these three count as one!
restore filelistonly from disk =
restore headeronly from disk =
restore verifyonly from disk =
3) dbcc sqlperf (LOGSPACE)
4) dbcc loginfo with tableresults
5) SQL Server Log
create table #ErrorLog(LogID int identity(1, 1) not null primary key, LogDate datetime null, ProcessInfo nvarchar(100) null, LogText nvarchar(4000) null);
insert into #ErrorLog (LogDate, ProcessInfo, LogText) exec master.dbo.xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, N'desc';
August 25, 2015 at 8:55 am
Steve Jones - SSC Editor (8/24/2015)
Wow some great lists. This might be a good thread to compile into an article.
Well, most of the tnings I listed are not scripts I would use wearing a DBA hat. In the days when I was responsible for lots of databases I hardly typed any SQL at all for DBA functions, everything was either automated and ran regularly delivering either exception reports or status reports or packaged into command line commands with server name or ip address or customer name (for all that customer's servers) parameters and in some cases a set of table names as parameters as well. That's because (a) I don't believe a DBA should waste time doing things that should be automated, and (b) once a DBA has invented a useful tool he shouldn't reinvent it every time he wants to use it; probably I acquired that attitude because I had far more work to do than just be a dba and a database developer.
I think aoms of the other commentors are coming from a wider base than just dBA too. So while there are some things that look like DBA scripts there are also some things that look more like DB developer scripts. Maybe these things need to be split - or maybe not?
Tom
August 25, 2015 at 9:42 am
Also not a DBA, more masquerading as a DBD. My top three:
select table_name, column_name
from information_schema.columns
where column_name like '%SomeText%'
order by table_name, column_name
exec sp_spaceused 'SomeTable'
exec sp_help 'SomeTable' -- via Alt-F1
August 25, 2015 at 7:50 pm
I made a point at not looking at other people's replies. I use WHOISACTIVE and sp_askbrent quite a bit but that's not what you're looking for. I haven't been doing much DBA - system maintenance work these days; mostly development... The most common for me are:
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
This is #1; I tune a lot of bad code
/*
*/
#2 Always blocking out chunks of code
RETURNS TABLE WITH SCHEMABINDING AS RETURN
#3... I replace a lot of scalar and multi-line functions...
IF OBJECT_ID('<whatever>') IS NOT NULL DROP <some object><whatever>;
#4... Makes things easier...
FROM sys.all_columns a, sys.all_columns b
#5... I create lots of sample data...
-- Itzik Ben-Gan 2001
August 25, 2015 at 8:09 pm
Brandie Tarvin (8/24/2015)
USE MyDatabase;
GO
SELECT Table_Name, Column_Name
FROM INFORMATION_SCHEMA.Columns
WHERE Column_Name LIKE 'MyColumn%'
ORDER BY Table_Name, Column_Name;
--Find all tables containing a certain column name
Yep, Amen! I forgot this... This (and/or variations of) should have been included in my TOP 5.
-- Itzik Ben-Gan 2001
August 26, 2015 at 5:27 am
DBCC SQLPERF (LOGSPACE)
select * from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text (r.sql_handle) st
select * from sys.sql_modules where definition like '%thing of interest%'
select * from sys.dm_exec_procedure_stats
Increasingly, ForEach... Invoke-SqlCmd... or gci SQLSERVER:\SQL\... 😉
But probably the most common: DELCARE left left left backspace left C 😛
August 26, 2015 at 7:10 am
Gazareth (8/26/2015)
But probably the most common: DELCARE left left left backspace left C 😛
Oh, yeah. I use that one quite often. But I don't think that's what Grant is looking for. :hehe:
August 26, 2015 at 7:19 am
Brandie Tarvin (8/26/2015)
Gazareth (8/26/2015)
But probably the most common: DELCARE left left left backspace left C 😛Oh, yeah. I use that one quite often. But I don't think that's what Grant is looking for. :hehe:
Yeah, he probably wants actual commands used. Can you imaging a keystroke article? It would never end. 😛
August 26, 2015 at 7:26 am
Gazareth (8/26/2015)
But probably the most common: DELCARE left left left backspace left C 😛
In that case, mine would be SEELCT left left backspace left L :hehe:
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2015 at 7:54 am
GilaMonster (8/26/2015)
Gazareth (8/26/2015)
But probably the most common: DELCARE left left left backspace left C 😛In that case, mine would be SEELCT left left backspace left L :hehe:
Interesting. My "favorite" typo is SLEECT, yours is SEELCT. I am pretty sure that psychologists could have a field day with this... :crazy:
August 26, 2015 at 7:59 am
Hugo Kornelis (8/26/2015)
GilaMonster (8/26/2015)
Gazareth (8/26/2015)
But probably the most common: DELCARE left left left backspace left C 😛In that case, mine would be SEELCT left left backspace left L :hehe:
Interesting. My "favorite" typo is SLEECT, yours is SEELCT. I am pretty sure that psychologists could have a field day with this... :crazy:
Mine if FORM instead of FROM. I think we all have them.
Viewing 15 posts - 31 through 45 (of 63 total)
You must be logged in to reply to this topic. Login to reply