What are your five most common T-SQL commands

  • Wow some great lists. This might be a good thread to compile into an article.

  • 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 Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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

  • 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://www.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.

  • 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';

  • 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

  • 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

  • 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...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • 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.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • 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 😛

  • 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:

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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. 😛

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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:


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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