Useful T-SQLs (Part I)

Raymond Tang Raymond Tang 0 545 0.13 index 9/2/2013

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;GOEXEC sp_detach_db @dbname = [SpaceElevator];GO

Attach Database

USE master;GOCREATE 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 * FROMOPENROWSET(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_BaconWITH 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;GOsp_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
sql sql-server t-sql

Join the Discussion

View or add your thoughts below

Comments