What are your five most common T-SQL commands

  • Here is a script I use to grant (or request for myself) "power user" permissions without the need for full sysadmin membership.

    -- server level permissions:

    use master

    -- grant user permission to view object schemas:

    grant view any definition to [poweruser];

    -- grant user permission to view system tables and views:

    grant view server state to [poweruser];

    -- grant user permission to start sql profiler traces:

    grant alter trace to [poweruser];

    GO

    -- database level permissions:

    use [thedatabase];

    -- grant select on any table or view:

    exec sp_addrolemember db_datareader, [poweruser]

    -- grant user permission to view execution plans:

    grant showplan to [poweruser];

    GO

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • You'd think that folks would know better, but having dealt with a large number of development, UAT, and production servers; this script actually returns some surprises on occasion. It may even be good to include this in a scheduled job or maintenace plan with an email alert.

    -- Query accounts with empty password:

    select @@servername servername, name, '' as pwtext, type_desc, create_date

    , modify_date, is_disabled, is_policy_checked, is_expiration_checked

    from sys.sql_logins

    where pwdcompare('', password_hash) = 1;

    -- Query accounts where password = account name:

    select @@servername servername, name, name as pwtext, type_desc, create_date

    , modify_date, is_disabled, is_policy_checked, is_expiration_checked

    from sys.sql_logins

    where pwdcompare(name, password_hash) = 1;

    -- There are several frequently used password lists posted on the web.

    -- Here are a few, but perhaps 100 or more could be inserted here.

    declare @PW table (pwtext varchar(180) not null primary key);

    insert into @PW (pwtext) values

    ('sa'),('dev'),('prod'),('admin'),('admin1'),('administrator')

    ,(''),('password'),('123456'),('12345678'),('1234'),('qwerty');

    select @@servername servername, name, pw.pwtext, type_desc, create_date

    , modify_date, is_disabled, is_policy_checked, is_expiration_checked

    from sys.sql_logins l

    join @PW pw on pwdcompare(pw.pwtext, l.password_hash) = 1;

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Here are mine

    sp_help 'tablename'

    sp_spaceused 'tablename'

    select si.name, si.is_disabled, iu.* from sys.dm_db_index_usage_stats iu join sys.indexes si on iu.object_id = si.object_id and iu.index_id = si.index_id where iu.object_id = object_id ('tablename')

    dbcc show_statistics ('tablename','indexname')

    dbcc sqlperf(logspace)

  • sp_helpdb

    Restore filelistonly

    Restore/backup database

    Set statistics io/time on

    sp_whoisactive

    MCSE Data Platform; BI
    MCITP Database Developer; BI

  • sp_helptext

    set statistics io on

    set statistics time on

    dbcc checkdb

    set transaction isolation level read uncommitted

  • Here are the ones I think I use the most:

    1:

    BEGIN TRAN;

    go

    go

    ROLLBACK TRAN;

    go

    (after that, I type something in between the two "go" lines. But I always start with this and then execute to ensure there are no syntax errors)

    2:

    SET STATISTICS IO ON;

    3:

    SET STATISTICS TIME ON;

    4:

    sp_who2;

    Okay, not quite five, but close.


    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/

  • 1.

    BEGIN TRY

    --

    --

    END TRY

    BEGIN CATCH

    --

    --

    END CATCH

    2.

    BEGIN TRAN

    --

    --

    COMMIT TRAN/ROLLBACK TRAN

    3.

    BACKUP DATABASE ----

    TO D:\BACKUP\---- ;

    4.

    SET TRANSACTION ISOLATION LEVEL ----

    5.

    USE PLAYPEN

    edit: Number 5 is maybe the one I use most. Number 2 next.

    Tom

  • To be honest, there's only one thing that I type very much and that's BEGIN TRANSACTION. As a DBA, I have pretty much everything else automated. I've even automated my "sp_ShowWorst" stored procedure, which keeps track of the top 20 consumers of CPU, IO, and several other categories every hour of the day. Other than my login information, the only other thing that I "type" on a regular basis is to run my "sp_WhatsRunning" proc. But, I don't type that either. I've got that bad boy hot-keyed.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • exec master..xp_fixeddrives

    select * from sys.database_files

    backup database DatabaseName to disk = 'FileLocation' with init, compression, copy_only, stats = 10

    restore database DatabaseName from disk = ''

    with

    move 'Data' to 'FileLocation'

    ,move 'Log' to 'FileLocation'

    ,replace

    ,recovery

    ,stats = 10

    -- Less overhead than activity monitor

    select

    ...

    from

    sys.dm_exec_sessions es

    left join sys.dm_exec_requests er on es.session_id = er.session_id

    cross apply sys.dm_exec_sql_text(er.sql_handle) st

    where

    er.wait_time > 0

    and es.session_id is not null

  • Used to generate a large number of rows.

    L0..L9 are used to generate a large number of rows.

    The lower parts are used to generate a specific 'code' in this case a number plate.

    With a simple replacement of the statements it is fairly easy to generate a codes in a desired format. (Cijfer stands for a single digit, Letter stands for a single Character).

    The top part of the code is used to generate a lot of rows, for many different situations.

    The bottom part of the code is adjusted for each requirement.

    WITH

    L0 AS(SELECT 0 AS c UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0), -- 4

    L1 AS(select 0 as x from L0 A, L0 B, L0 C, L0 D), -- 4 ^4 = 256

    L2 AS(select 0 as x from L1 A, L1 B, L1 C, L1 D), -- (4 ^ 4) ^4 = 4 Giga

    L9 AS(Select row_number() OVER(PARTITION BY 1 order by x ) as P from L2) -- voeg rijnummers toe

    ,

    Letter0 as (Select CHAR(P-1+ASCII('A')) as P FROM L9 where P <= 26),

    Letter as (SELECT * FROM Letter0 WHERE P NOT IN ('A','O','I','U','E','Q')),

    Cijfer as (Select CHAR(P-1+ASCII('0')) as P FROM L9 where P <= 10),

    plate as (select a.P+b.P+'--'+c.P+d.P+'--'+e.P+f.P plate_number,

    ROW_NUMBER() over(partition by 1 ORDER BY (SELECT NULL)) volg

    from cijfer a,cijfer b,letter c,letter d,cijfer e,cijfer f)

    SELECT TOP 100 * FROM plate where volg > 1000000 and volg <= 1000333

    A second one to determine differences between two tables:

    select * from A

    except

    select * from B

    Sometimes this is done on temp tables or copies of tables, to see what has changed during some time. The order of selects is sometimes swapped as wel to see the differences in the other direction.

    A third one:

    exec sp_drop A -- to drop a table if the table exists.SP_Drop is coded in the master table and when the table exists the table gets dropped.

    This works for tables in the current database, for #tables and for ##tables.

    If the table does not exist no action is taken

    Ben

  • I will add some others later, but here's one I frequently use for our non-prod environments.

    SELECT TOP 10 *

    FROM msdb.dbo.restorehistory

    WHERE destination_database_name = 'MyDatabase'

    ORDER BY restore_date DESC;

    --check the last time a certain non-prod database restore was done

    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

    EDIT: This is a recent script that is not complete. There are still a few datatypes I need to add to it in order to find the sizes. But it works fairly well for what I currently use with temptables.

    DECLARE @TableName VARCHAR(50);

    SET @TableName ='tempdb..#MyTempTable'; -- change this to your table name

    SELECT c.name + ', ' AS ColumnName, c.name + ' ' + UPPER(t.name) +

    CASE WHEN t.name IN ('VARCHAR','CHAR')

    THEN '('+CAST(c.max_length AS VARCHAR(3) )+'),'

    WHEN t.name IN ('DECIMAL','NUMERIC','FLOAT')

    THEN '('+CAST(c.precision AS VARCHAR(3) )+','

    + CAST(c.scale AS VARCHAR(3) )+'),'

    ELSE ',' END AS ColumnDataType

    FROM tempdb.sys.columns c

    INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id

    WHERE object_id = object_id(@TableName )

    ORDER BY c.column_id

    --For discovering column datatypes and sizes on SELECT INTO created temp tables

    Ordering the results of sp_who2.

    --NOTE: This is NOT my script. I got it off StackOverflow

    --http://stackoverflow.com/questions/2234691/sql-server-filter-output-of-sp-who2

    --The WHERE Clause and ORDER BY is all mine, though.

    DECLARE @Table TABLE(

    SPID INT,

    Status VARCHAR(MAX),

    LOGIN VARCHAR(MAX),

    HostName VARCHAR(MAX),

    BlkBy VARCHAR(MAX),

    DBName VARCHAR(MAX),

    Command VARCHAR(MAX),

    CPUTime INT,

    DiskIO INT,

    LastBatch VARCHAR(MAX),

    ProgramName VARCHAR(MAX),

    SPID_1 INT,

    REQUESTID INT

    )

    INSERT INTO @Table EXEC sp_who2

    SELECT * FROM @Table

    /* The WHERE clause is used to filter results

    by HostNames or Login names or Database names, etc. */

    --WHERE -

    -ProgramName LIKE 'Microsoft%'

    --LOGIN = 'Domain\Login'

    --HostName = 'MyServerName'

    --DBName LIKE 'Report%'

    ORDER BY BlkBy, SPID

    -- I use BlkBy and SPID when looking for blocked processes

    --otherwise, I order by DBName, etc.

    And here's a good one for checking progress on restores, backups, and database shrinks:

    SELECT percent_complete, blocking_session_id, *

    FROM sys.dm_exec_requests r

    INNER JOIN sys.dm_os_tasks t

    ON r.session_id = t.session_id

    WHERE r.session_id = 138; --change this SPID as necessary

    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.

  • 1. begin transaction

    2. set statistics time, io on

    3. select t.N from util.dbo.Tally1K (a 1000-row tally table available to everyone)

    4. dateadd(day, 0, datediff(day, 0, getdate())) --many times on a column instead

    5. backup database

    Honestly, I have a great many frequently-used things automated. Things like getting the space allocated versus used, restoring a database from a specific backup file that I copied to another server, finding permissions for a specific login, etc. are all automated so they're always available and repeatable. Maybe a section of the article on automating things you do frequently would be too much scope creep.

    I hope this gives you some help for your article. I look forward to reading it.

  • sp_whoisactive

    sp_who2

    sp_lock

  • These would be for when I'm working on client systems.

    In no particular order:

    SELECT * FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st;

    SELECT TOP(1000) * FROM sys.dm_exec_query_stats qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

    ORDER BY total_worker_time DESC; -- or total elapsed time, or total logical reads

    SELECT * FROM sys.dm_os_wait_stats

    ORDER BY wait_time_ms DESC; -- quick and dirty

    SELECT * FROM sys.configurations

    ORDER BY name;

    SELECT * FROM sys.databases;

    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
  • -- Variations on the following frequently including sys.dm_exec_sql_text and sys.dm_exec_query_plan

    SELECT blocking_session_id,* FROM sys.dm_exec_requests WHERE session_id > 50

    EXECUTE AS LOGIN = 'AD Login'

    EXEC sp_helpindex [tablename]

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Viewing 15 posts - 16 through 30 (of 63 total)

You must be logged in to reply to this topic. Login to reply