Useful T-SQLs (Part I)

There are a lot of useful T-SQLs we can use in daily work to make things simple.

Check transaction log files space usage

DBCC SQLPERF (LOGSPACE);

Backup Server Certificate

BACKUP CERTIFICATE ServerCertificate
TO FILE = 'ServerCertExport'
WITH PRIVATE KEY
(
FILE = 'PrivateKeyFile',
ENCRYPTION BY PASSWORD = '<PrivateKeyPasswordHere>'
);
GO

Check Oldest Running Transaction

DBCC OPENTRAN;

User Options

DBCC USEROPTIONS;

Table Statistics

DBCC SHOW_STATISTICS ('Context.BlogPosts',PK_BlogPosts) WITH HISTOGRAM;

Check Database

DBCC CHECKDB('ContextProject_DEV', REPAIR_REBUILD)

Find all the DBCC Commands

DBCC HELP(CHECKTABLE)

The output will be:

dbcc CHECKTABLE 
(
    { 'table_name' | 'view_name' }
    [ , NOINDEX
    | index_id
    | { REPAIR_ALLOW_DATA_LOSS
    | REPAIR_FAST
    | REPAIR_REBUILD
    } ]
)
    [ WITH
        {
            [ ALL_ERRORMSGS ]
            [ , [ NO_INFOMSGS ] ]
            [ , [ TABLOCK ] ]
            [ , [ ESTIMATEONLY ] ]
            [ , [ PHYSICAL_ONLY ] ]
            [ , [ EXTENDED_LOGICAL_CHECKS  ] ]
        }
    ]
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Manually Force a Checkpoint

CHECKPOINT

Detach Database

USE master;
GO
EXEC sp_detach_db @dbname = [SpaceElevator];
GO

Attach Database

USE master;
GO
CREATE DATABASE SpaceElevator ON (Filename = 'C:\SpaceElevator\SpaceElevator.mdf'),
(FILENAME = 'C:\SpaceElevator\SpaceElevator_log.ldf') FOR ATTACH;
GO

Import Data from File

BULK INSERT ExampleDatabase.dbo.TableAlpha FROM '\\SQL-A\DATA\products.txt';
INSERT INTO X SELECT * FROM
OPENROWSET(BULK) ()

Connecting to Remote Data Sources

SELECT * FROM OPENDATASOURCE('SQLNCLI', 'Data Source=London\Payroll;Integrated Security=SSPI') .AdventureWorks2012.HumanResources.Employee;
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\DataFolder\Documents\TestExcel.xls;Extended Properties=EXCEL 5.0')...[Sheet1$] ; 
OPENQUERY ( linked_server ,'query' ) 

Create SQL Server Login Using Certificate or Asymmetric  Key

CREATE CERTIFICATE Dan_Bacon
WITH SUBJECT = 'Dan Bacon certificate in master database',
EXPIRY_DATE = '01/01/2018';
CREATE LOGIN Dan_Bacon FROM CERTIFICATE Dan_Bacon;
CREATE ASYMMETRIC KEY sql_user_e WITH ALGORITHM = RSA_2048;
CREATE LOGIN sql_user_e FROM ASYMMETRIC KEY sql_user_e;

Find Orphaned Database Users

sp_change_users_login @Action='Report';

Enable Contained User Authentication (SQL2012)

sp_configure 'show advanced', 1;
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'contained database authentication', 1;
RECONFIGURE WITH OVERRIDE;
GO

Change Table Schema

ALTER SCHEMA Lockdown TRANSFER dbo.Engines;

Find all the Endpoints

select * from sys.endpoints