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