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

About author
Disclaimer
The opinions and comments expressed herein are my own personal opinions and do not represent my employer's view in any way.
Comments
No comments.
Add comment
Title
Title is required.
Name
Name is required.
Email
Please input your personal email with valid format.
Comments
Please input comment content.
Captcha Refresh
Input captcha:

Subscription

Statistics

Locations of visitors to this page