access_time 8 years ago languageEnglish
more_vert

Useful T-SQLs (Part I)

visibility 433 comment 0

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

info Last modified by Raymond 7 years ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn.

Want to contribute on Kontext to help others?

Learn more

More from Kontext

visibility 405
thumb_up 0
access_time 8 years ago
visibility 1347
thumb_up 0
access_time 8 years ago