A lot of times we need to find out how to do things in SQL Server. Like searching for a procedure that contains certain word, find out indexes which are not used, fix orphaned users, and disable an index. In this article I’m trying to list down, as many as possible, the scripts for doing everyday stuff in SQL Server.
/* SQL Server Script Library Author: Vincent Rainardi Date written: 4th Dec 2014 Last updated: 2nd Feb 2015 CREATE DATABASE OBJECTS 1. Create a database 2. Create a filestream database 3. Drop a database 4. Rename a database 5. Create a login 6. Delete a login 7. Create a user 8. Delete a user 9. Create a schema 10. Drop a schema 11. Create a table 12. Create a memory-optimized table 13. Delete a table 14. Create a filetable 15. Delete a filetable 16. Create a view 17. Delete a view 18. Create an index 19. Create a filtered index 19a. Force using index 20. Create a spatial index 21. Delete an index 22. Create statistics 23. Show statistics 23a. When the statistic is last updated 24. Update statistics 25. Delete a statistics 26. Create a constraint 26a. Rename a constraint 27. Drop a constraint 28. Create a primary key 29. Delete a primary key 30. Create a foreign key 31. Delete a foreign key 32. Disable a foreign key 33. Enable a foreign key 34. Disable an index 35. Enable an index 36. Rename an index 37. Create a columnstore index 38. Delete a columnstore index 39. Add a column 40. Drop a column 41. Rename a column 42. Change data type of a column 43. Create a computed column 44. Create a synonym 45. Drop a synonym 46. Enable partitioning on the server 47. Disable partitioning on the server 48. Create a partitioned table 49. Drop a partitioned table 50. Create a partitioned index 51. Delete a partitioned index 52. Create an indexed view 53. Drop an indexed view 54. Add an extended property 55. Delete an extended property 56. Enable CDC on the server 57. Disable CDC on the server 58. Enable CDC on a table 59. Disable CDC on a table 59a. View the changed rows in the CDC table 60. CDC functions 61. CDC stored procedures 62. Enable Change Tracking on the database 63. Disable Change Tracking on the database 64. Enable Change Tracking on a table 65. Disable Change Tracking on a table 66. Change Tracking functions 67. Create a trigger 68. Delete a trigger 69. Disable a trigger 70. Enable a trigger DATABASE DEVELOPMENT 1. Select rows 2. Update rows 3. Insert rows 4. Delete rows 5. Grouping rows 6. Inner join 7. Left and right join 8. Full outer join 9. Join hint 10. Like operator 11. Collation 12. Correlated subquery 13. Select into 14. Insert Select 14a. Output an SP into a table 15. Update from 16. Delete from 17. Truncate table 18. Cast 19. Convert 20. NULLIF 21. ISNULL 22. Coalesce 23. Union and union all 24. Declare a variable 25. Create a temp table 26. Drop a temp table 27. Create global temp table 28. Drop global temp table 29. Create a table variable 30. Memory-optimized table variables 31. Looping on a table variable 32. Delete from a table variable 33. Create a stored procedure 34. Execute a stored procedure 35. Drop a stored procedure 36. Create a scalar function 37. Create a table valued function 38. Create a CLR function 39. Delete a CLR function 40. Cross apply 41. Outer apply 42. While 43. Continue 44. Case when 45. If else 46. Try catch 47. Merge 48. Upsert 49. Checksum 50. String functions 51. Date functions 52. Set date format 53. Transaction 54. Pivot 55. Unpivot 56. Print the current time 57. Using row number 58. Find duplicate rows 59. Remove duplicate rows 60. Rank and dense rank 61. Choose function 62. IIF function 63. Except 64. Intersect 65. Dynamic SQL 66. Using Cursor 67. Cursor stored procedures 68. Put output of SP into a table 69. Select for XML 70. Prepare XML document 71. Remove XML document 72. Querying XML data 73. Option recompile 74. Bitwise operation 75. Waitfor 76. Applock DATABASE ADMINISTRATION 1. Create a maintenance plan 2. Backup database 3. Restore database 4. Get database state 5. Get database size 6. Detact a database 7. Attach a database 8. Add a file group 9. Add a file to a database 10. Delete a file from a database 11. Move a database file 12. Inrease the size of a database 13. Reorganise an index 14. Rebuild an index 15. Check for fragmentation 16. Check size of log file 17. Check Log Sequence Number (LSN) 18. Check the last the a job run 19. Check the last time a table is accessed 20. Check the last time a user logged in 21. Create a database snapshot 22. Drop a database snapshot 23. Check for blocking 24. Check for locks 25. Check for deadlocks 26. Set deadlock priority 27. Set lock timeout 28. Check for orphaned users 29. Fix orphaned users 30. Find out the largest tables 31. Find out database space used 32. Get SQL Server version 33. Get memory used 34. Get number of connections 35. Create a job 36. Create a job step 37. Create a job schedule 38. Delete a job schedule 39. Delete a job step 40. Delete a job 41. Create a linked server to a SQL Server 42. Create a linked server to a SSAS 43. Drop a linked server 44. Find out who is connected 45. Create a role 46. Drop a role 47. Add a member to a role 48. Remove a member from a role 49. Grant permission to a stored procedure 50. Deny execution on a stored procedure 51. Grant permission to a table 52. Deny permission on a table 52a. Find out permissions 53. BCP 54. Create a trace 55. Delete a trace 56. Truncate transaction log 57. Bulk import 58. Bulk export 59. Enable data compression on a table 60. Disable data compression on a table 61. Checkpoint 62. Setup database mirroring 63. Monitoring database mirroring 64. Pausing a database mirroring session 65. Resuming a database mirroring session 66. Removing database mirroring 67. Create a server audit 68. View a SQL Server audit log 69. Cryptographic functions 70. List of all databases 71. List of columns in a table 72. List of columns in an index 73. List of statistics in an index 74. List of partitions in a table 75. Display view definition 76. Display table details 77. Find a column 78. Find an index 79. Find a view 80. Find a stored procedure 81. Find tables without a primary key 82. Find tables without an index 83. Find tables which were modified today 84. Find primary keys columns 85. Find foreign keys columns 86. Find columns with certain data types 87. Find a constraint 88. Find a trigger 89. Find a synonym 90. Find all stored procedures using a certain table 91. Find parameters in a stored procedure 92. Find owners the tables in a database 93. Find tables owned by a certain users 94. Detect page splits 95. Change isolation levels 96. Delete data from a large table 97. Row count on a large table 98. Max is too slow 99. Set row count 100. Display how long it takes to run a query 101. Display disk activity when a query runs 102. Kill a connection 103. Policy stored procedures 104. DB engine stored procedures 105. Security stored procedures */ -- PART 1. CREATE DATABASE OBJECTS -- 1. Create database CREATE DATABASE Database1 ON PRIMARY ( NAME = 'Database1_Data', FILENAME = 'E:\MSSQL\Data\Database1_Data.mdf', SIZE = 10 GB, MAXSIZE = 100 GB, FILEGROWTH = 5 GB ), FILEGROUP SECONDARY ( NAME = 'Database1_Index', FILENAME = 'F:\MSSQL\Data\Database1_Index.mdf', SIZE = 4 GB, MAXSIZE = 30 GB, FILEGROWTH = 2 GB ) LOG ON ( NAME = 'Database1_Log', FILENAME = 'G:\MSSQL\DATA\Database1_Log.ldf', SIZE = 1 GB, FILEGROWTH = 512 MB ) GO -- Suffix: KB, MB, GB, TB -- Data file: min 5 MB to accomodate Model DB, max 16 TB -- Log file: min 512 KB, max 2 TB -- File growth: min 64 KB, rounded to nearest 64 KB -- 2. Create a filestream database -- To enable filestream: Configuration Manager, SQL Server Properties, FILESTREAM tab, then: EXEC sp_configure filestream_access_level, 2 RECONFIGURE GO CREATE DATABASE Database2 ON PRIMARY ( NAME = 'Database2_Data', FILENAME = 'E:\MSSQL\Data\Database2_Data.mdf', SIZE = 10 GB, MAXSIZE = 100 GB, FILEGROWTH = 5 GB ), FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM ( NAME = 'Database2_Filestream', FILENAME = 'F:\MSSQL\Filestream\Database2' ) LOG ON ( NAME = 'Database2_Log', FILENAME = 'G:\MSSQL\DATA\Database2_Log.ldf', SIZE = 1 GB, FILEGROWTH = 512 MB ) GO ALTER DATABASE [Database2] SET FILESTREAM (DIRECTORY_NAME = N'Database2') WITH NO_WAIT GO -- 3. Drop database IF EXISTS (SELECT * FROM SYS.DATABASES WHERE NAME = 'Database1') DROP DATABASE Database1 -- Compatibility view: SYSDATABASES -- 4. Rename a database ALTER DATABASE Database1 MODIFY Name = Database2 GO -- 5. Create a login CREATE LOGIN [DOMAIN\SQL Guy] FROM WINDOWS WITH DEFAULT_DATABASE=[Database1] GO -- 6. Delete a login IF EXISTS (SELECT * FROM SYS.SYSLOGINS WHERE NAME = 'DOMAIN\SQL Guy') DROP LOGIN [DOMAIN\SQL Guy] GO -- Compatibility view: SYSLOGINS -- 7. Create a user CREATE USER [DOMAIN\SQL Guy] FOR LOGIN [DOMAIN\SQL Guy] GO -- 8. Delete a user IF EXISTS (SELECT * FROM SYS.SYSUSERS WHERE NAME = 'DOMAIN\SQL Guy') DROP USER [DOMAIN\SQL Guy] GO -- Drop user automatically drop their memberships -- Compatibility view: SYSUSERS -- 9. Create schema CREATE SCHEMA Schema1 AUTHORIZATION [dbo] GO -- Create schema must be the only statement in the batch -- 10. Drop a schema IF EXISTS (SELECT * FROM SYS.SCHEMAS WHERE NAME = 'Schema1') DROP SCHEMA Schema1 GO -- 11. Create a table CREATE TABLE dbo.Table1 ( Column1 INT NOT NULL IDENTITY (1, 1), Column2 NVARCHAR(10), Column3 DATE, Column4 DECIMAL(14, 5), CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED (Column1 ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO -- 12. Create a memory-optimized table CREATE TABLE dbo.Table2 ( Column1 INT NOT NULL IDENTITY(1,1) PRIMARY KEY NONCLUSTERED, Column2 INT INDEX NC_Column2 NONCLUSTERED HASH WITH (BUCKET_COUNT=1000), Column3 DATETIME2 ) WITH (MEMORY_OPTIMIZED=ON) GO -- 13. Delete a table IF OBJECT_ID('dbo.Table1') IS NOT NULL DROP TABLE dbo.Table1 GO -- Compatibility view: SYSOBJECTS -- 14. Create Filetable CREATE TABLE dbo.FileTable1 AS FILETABLE WITH ( FILETABLE_DIRECTORY = 'E:\MSSQL\Folder1', FILETABLE_COLLATE_FILENAME = database_default ) GO -- 15. Delete Filetable IF OBJECT_ID('dbo.FileTable1') IS NOT NULL DROP TABLE dbo.FileTable1 GO -- 16. Create a view CREATE VIEW View1 AS SELECT T1.Column1, T2.Column2, T1.Column3 FROM Table1 T1 JOIN Table2 T2 on T1.Column1 = T2.Column2 WHERE T2.Column3 = 'Active' -- Create view must be the only statement in the batch -- 17. Delete a view IF OBJECT_ID('dbo.View1') IS NOT NULL DROP VIEW dbo.View1 -- 18. Create an index CREATE INDEX NC_Table1_Column2Column4 ON dbo.Table1 (Column2 DESC,Column4 ASC) --This is NONCLUSTERED CREATE CLUSTERED INDEX CI_Table1_Column1 ON dbo.Table1 (Column1) --Can take a long time if the table has 1 million rows -- 19. Create a filtered index CREATE INDEX FI_Table1_Column2Column4WithColumn3NotNull ON dbo.Table1 (Column2 DESC,Column4 ASC) WHERE Column3 IS NOT NULL -- 19a. Force using index SELECT Column2, Column3 FROM TABLE1 WITH ( INDEX(FI_Table1_Column2Column4WithColumn3NotNull) ) WHERE Column3 = 'FKA' -- 20. Create a spatial index CREATE SPATIAL INDEX SI_Table1_Column1 ON Table1 (Column1) USING GEOMETRY_GRID WITH ( Bounding_Box = (0,0,100,100), GRIDS = LOW, LOW, MEDIUM, HIGH, CELLS_PER_OBJECT = 64, PAD_INDEX = ON ) -- 21. Delete an index IF EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'NC_Table1_Column2Column4') DROP INDEX NC_Table1_Column2Column4 ON dbo.Table1 -- Compatibility view: sysindexes -- 22. Create statistics CREATE STATISTICS Stat1 ON Table1(Column1, Column2) --By default query optimizer determines the statistically significant sample size CREATE STATISTICS Stat1 ON Table1(Column1, Column2) WHERE Column3 &amp;amp;amp;amp;amp;gt; '2014-01-01' --Filtered Statistics CREATE STATISTICS Stat1 ON Table1(Column1, Column2) WITH SAMPLE 20 PERCENT --The optimizer scans all rows on a data page so the actual number is not 20% -- 23. Show statistics DBCC SHOW_STATISTICS ('dbo.Table1', Index1) --to specify schema use single or double aposthrophy DBCC SHOW_STATISTICS (Table1, Index1) WITH HISTOGRAM --RANGE_ROWS = number of rows in each step/bucket DBCC SHOW_STATISTICS (Table1, Index1) WITH STAT_HEADER --Density is not used, Average key length is in bytes DBCC SHOW_STATISTICS (Table1, Index1) WITH DENSITY_VECTOR --Density = 1 / (Distinct Values - 200) SELECT * FROM sys.stats WHERE object_id = object_id('Table1') SELECT * FROM sys.stats_columns WHERE object_id = object_id('Table1') -- 23a. When the statistic is last updated SELECT Name, stats_date(object_id,stats_id) FROM sys.stats WHERE object_id = object_id('Table3') --Can be used for both index and statistic SELECT Name, stats_date(object_id,index_id) FROM sys.indexes WHERE object_id = object_id('Table3') and Type_Desc &amp;amp;amp;amp;amp;lt;&amp;amp;amp;amp;amp;gt; 'HEAP' --For index -- 24. Update statistics UPDATE STATISTICS Table1(Index1) EXEC sp_updatestats -- All tables -- 25. Delete a statistics DROP STATISTICS Table1.Stat1 --Can't drop statistics of an index -- 26. Create a constraint ALTER TABLE Table1 ADD CONSTRAINT Constraint1 UNIQUE (Column2, Column3) --Unique constraint. This creates a unique index. ALTER TABLE Table1 ADD CONSTRAINT Constraint1 CHECK (Column2 &amp;amp;amp;amp;amp;lt; 10) --Check constraint ALTER TABLE Table1 ADD CONSTRAINT Constraint1 PRIMARY KEY (Column1) --Primary Key constraint ALTER TABLE Table1 ADD CONSTRAINT Constraint1 FOREIGN KEY (Column1) REFERENCES Table2(Colomn1) --Foreign Key constraint ALTER TABLE Table1 ALTER COLUMN Coloumn1 INT NOT NULL --NOT NULL constraint -- 26a. Rename a constraint EXEC sp_rename 'Constraint1', 'Constraint2' --Single quotes are annoying in WordPress -- 27. Drop a constraint ALTER TABLE Table1 DROP CONSTRAINT Constraint1 -- 28. Create a primary key ALTER TABLE Table1 ADD CONSTRAINT PK1 PRIMARY KEY CLUSTERED (Column1, Column2) --Best: clustered PK on an identity column -- 29. Delete a primary key ALTER TABLE Table1 DROP CONSTRAINT PK1 --Takes a long time to recreate a Clustered PK -- 30. Create a foreign key ALTER TABLE Table1 ADD CONSTRAINT FK1 FOREIGN KEY (Column1) REFERENCES Table2(Colomn1) --Table2 must have PK on Column1 -- 30a. View foreign keys information SELECT * FROM sys.foreign_keys --Compatibility view: sys.sysforeignkeys SELECT FK.name AS FKName, object_name(FK.parent_object_id) AS ChildTable, col_name(FK.parent_object_id, FKC.constraint_column_id) as ChildColumn, object_name(FK.referenced_object_id) AS ParentTable, --In ANSI standard, "Parent table" is the table with the PK. But in SQL Server, "Parent table" is the table with the FK. col_name(FKC.referenced_object_id, FKC.referenced_column_id) AS ParentColumn FROM sys.foreign_keys FK JOIN sys.foreign_key_columns AS FKC ON FK.object_id = FKC.constraint_object_id -- 31. Delete a foreign key ALTER TABLE Table1 DROP CONSTRAINT FK1 -- 32. Disable a foreign key ALTER TABLE Table1 NOCHECK CONSTRAINT FK1 --This is just disabling for UPDATE and INSERT, but not for Replication ALTER TABLE Table1 NOCHECK CONSTRAINT ALL --Disable all FKs on Table1 (not PK, Check & Unique constraints) --Disable all FKs on all tables: EXECUTE sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' -- 33. Enable a foreign key ALTER TABLE Table1 WITH CHECK CHECK CONSTRAINT FK1 --Enabling for UPDATE and INSERT, not for Replication ALTER TABLE Table1 WITH CHECK CHECK CONSTRAINT ALL --Enable all FKs on Table1 (not PK, Check & Unique constraints --Enable all FKs on all tables: EXECUTE sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL' -- 34. Disable an index ALTER INDEX Index1 ON Table1 DISABLE --if the Clustered Index is disabled, NC indexes will be automatically disabled ALTER INDEX ALL ON Table1 DISABLE --disable all indexes on Table1 -- 35. Enable an index ALTER INDEX Index1 ON Table1 REBUILD ALTER INDEX ALL on Table1 REBUILD --enable all indexes on Table1. Can be used for all indexes. CREATE INDEX Index1 ON Table1(Column2) WITH (DROP_EXISTING = ON) --Cannot be used for indexes for Unique Constraint DBCC DBREINDEX (Table1, Index1) --Can be used for all indexes -- 36. Rename an index IF EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'Index1') EXEC sp_rename 'Table1.Index1', 'Index2', 'INDEX' -- 37. Create a columnstore index IF NOT EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'Index1') CREATE COLUMNSTORE INDEX Index1 ON Table1(Column1) -- 38. Delete a columnstore index IF EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'Index1') DROP INDEX Table1.Index1 -- 39. Add a column IF NOT EXISTS ( SELECT * FROM sys.columns WHERE name = 'Column1' and object_id = object_id('Table1') ) ALTER TABLE Table1 ADD Column1 INT -- 40. Drop a column IF EXISTS ( SELECT * FROM sys.columns WHERE Name = 'Column1' and object_id = object_id('Table1') ) ALTER TABLE Table1 DROP COLUMN Column1 -- 41. Rename a column IF EXISTS ( SELECT * FROM sys.columns WHERE Name = 'Column1' and object_id = object_id('Table1') ) EXEC sp_rename 'Table1.Column1', 'Column2', 'COLUMN' -- 42. Change data type of a column IF EXISTS ( SELECT * FROM sys.columns WHERE Name = 'Column1' and object_id = object_id('Table1') ) ALTER TABLE Table1 ALTER COLUMN Col1 DECIMAL (9,2) -- 43. Create a computed column IF NOT EXISTS ( SELECT * FROM sys.columns WHERE Name = 'Column3' and object_id = object_id('Table1') ) ALTER TABLE Table1 ADD Column3 AS Column1 * Column2 -- 44. Create a synonym IF NOT EXISTS (SELECT * FROM sys.synonyms WHERE name = 'Synonym1') CREATE SYNONYM Synonym1 FOR Server1.Database1.Schema1.Table1 -- 45. Drop a synonym IF EXISTS (SELECT * FROM sys.synonyms WHERE name = 'Synonym1') DROP SYNONYM Synonym1 -- 46. Enable more than 15000 partitions (on 2008 R2) EXEC sp_db_increased_partitions 'Database1', 'ON' --We can replace ON with TRUE EXEC sp_db_increased_partitions 'Database1' -- to check -- 47. Disable more than 15000 partitions (on 2008 R2) EXEC sp_db_increased_partitions 'Database1', 'OFF' --We can replace OFF with FALSE. Ensure no tables has more than 1000 partitions first. Set recovery model to simple to break the log chain. -- 48. Create a partitioned table CREATE PARTITION FUNCTION PF1 (INT) AS RANGE LEFT FOR VALUES (20140101, 20140201, 20140301) GO CREATE PARTITION SCHEME PS1 AS PARTITION PF1 ALL TO ([PRIMARY]) --Ideally on different file groups located on different disks GO CREATE TABLE Table1 ( Column1 INT, Column2 VARCHAR(20) ) ON PS1 (Colomn1) GO -- 48a. Find out if a table is partitioned or not SELECT * FROM sys.tables T JOIN sys.indexes I on T.object_id = I.object_id and I.[TYPE] in (0,1) --0 is heap, 1 is clustered index JOIN sys.partition_schemes PS on PS.data_space_id = I.data_space_id WHERE T.Name = 'Table1' -- 49. Drop a partitioned table DROP TABLE Table1 GO DROP PARTITION SCHEME PS1 GO DROP PARTITION FUNCTION PF1 GO -- 50. Create a partitioned index IF NOT EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'Index1' AND object_id = object_id('Table1')) CREATE INDEX Index1 ON Table1(Column1, Column2) --Must include the partitioning column. If we don't, SQL Server will add it automatically. IF NOT EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'Index1' AND object_id = object_id('Table1')) CREATE CLUSTERED INDEX Index1 ON Table1(Column1, Column2) --Must include the partitioning column. -- 51. Delete a partitioned index IF EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'Index1' AND object_id = object_id('Table1')) DROP INDEX Table1.Index1 -- 52. Create an indexed view IF NOT EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'Index1' AND object_id = object_id('View1', 'VIEW')) CREATE UNIQUE CLUSTERED INDEX Index1 ON View1(Column1) -- The view must not contain LEFT/RIGHT JOIN, SELECT in FROM, UNION, DISTINCT, MIN/MAX, CTE, COUNT, ORDER BY, PIVOT, OVER, APPLY. -- The view must be created using WITH SCHEMABINDING. -- The first indexed view must be unique clustered. -- 53. Drop an indexed view IF EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = 'Index1' AND object_id = object_id('View1', 'VIEW')) DROP INDEX View1.Index1 -- 54. Add an extended property IF NOT EXISTS (SELECT * FROM fn_listextendedproperty('Description',NULL,NULL,NULL,NULL,NULL,NULL)) EXEC sp_addextendedproperty @name = N'Description', --Add extended property on the database @value = 'Risk Data Warehouse'; IF NOT EXISTS (SELECT * FROM fn_listextendedproperty('Description','SCHEMA','dbo','TABLE','Customer',NULL,NULL)) EXEC sp_addextendedproperty @name = 'Description', --Add extended property on a table @value = 'This table stores the customer details', @level0type = 'SCHEMA', @level0name = 'dbo', @level1type = 'TABLE', @level1name = 'Customer' IF NOT EXISTS (SELECT * FROM fn_listextendedproperty('Description','SCHEMA','dbo','TABLE','Account','COLUMN','Balance')) EXEC sp_addextendedproperty @name = 'Description', --Add extended property on a column @value = 'This column contains the balance of the account on a particular date', @level0type = 'SCHEMA', @level0name = 'dbo', @level1type = 'TABLE', @level1name = 'Account', @level2type = 'COLUMN',@level2name = 'Balance' -- 54a. View an extended property SELECT O.Name as ObjectName, C.Name as ColumnName, EP.Name as ExtPropName, EP.Value as ExtPropValue FROM sys.extended_properties EP JOIN sys.objects O on O.object_id = EP.major_id LEFT JOIN sys.columns C on C.object_id = EP.major_id and C.column_id = EP.minor_id WHERE EP.class_desc = 'OBJECT_OR_COLUMN' --View the extended properties of all tables and columns SELECT * FROM fn_listextendedproperty(NULL,NULL,NULL,NULL,NULL,NULL,NULL) -- View the extended properties of the database SELECT * FROM fn_listextendedproperty(NULL,'SCHEMA','dbo','TABLE','Table1',NULL,NULL) --View the extended properties of Table1 -- 55. Delete an extended property IF EXISTS (SELECT * FROM fn_listextendedproperty('Description','SCHEMA','dbo','TABLE','Customer',NULL,NULL)) EXEC sp_dropextendedproperty @name = 'Description', --Delete an extended property on a table @level0type = 'SCHEMA', @level0name = 'dbo', @level1type = 'TABLE', @level1name = 'Customer' IF EXISTS (SELECT * FROM fn_listextendedproperty('Description','SCHEMA','dbo','TABLE','Account','COLUMN','Balance')) EXEC sp_dropextendedproperty @name = 'Description' --Delete an extended property on a column ,@level0type = 'SCHEMA', @level0name = 'dbo' ,@level1type = 'TABLE', @level1name = 'Account' ,@level2type = 'COLUMN', @level2name = 'Balance'; -- 56. Enable CDC on the database EXEC sys.sp_cdc_enable_db --Enable the current DB for CDC -- 57. Disable CDC on the database EXEC sys.sp_cdc_disable_db --Disable the current DB for CDC -- 58. Enable CDC on a table IF EXISTS ( SELECT * FROM sys.tables WHERE object_id = object_id('dbo.Table') AND is_tracked_by_cdc = 0 --Check that the table has not been enabled for CDC ) EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'Table1', @role_name = NULL --CDC table will be created on the default filegroup EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'Table1', @role_name = 'Role1', --To control access to the change data @filegroup_name = 'Filegroup1', --CDC table will be created on Filegroup1 @supports_net_changes = 1 --Generate this function: cdc.fn_cdc_get_net_changes_<capture_instance>. When a row had several changes applied to it, this function returns the final state of the row. -- 59. Disable CDC on a table IF EXISTS ( SELECT * FROM sys.tables WHERE object_id = object_id('dbo.Table1') AND is_tracked_by_cdc = 1 --Check if the table is enabled for CDC ) EXEC sys.sp_cdc_disable_table @source_schema = 'dbo', @source_name = 'Table1', @capture_instance = 'dbo_Table1' --Get the capture instance: SELECT capture_instance FROM cdc.change_tables WHERE object_id = object_id('cdc.dbo_Table1_CT') -- 59a. View the changed rows in the CDC table select * from cdc.dbo_Table1_CT --$operation: 1 = delete, 2 = insert, 3 = before update, 4 = after update -- 60. CDC functions DECLARE @from_lsn binary(10), @to_lsn binary(10) SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Table1') --dbo.Table1 will return 0 hexa, which in turn will cause the get_all_changes function to say: An insufficient number of arguments were supplied. SET @to_lsn = sys.fn_cdc_get_max_lsn() SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Table1 (@from_lsn, @to_lsn, 'all') --Replacing 'all' with 'all update old' gives us the old values as well, on updated rows. select sys.fn_cdc_has_column_changed ( 'dbo_Table1','Column3' , 0x0F ) -- 61. CDC stored procedures EXEC sys.sp_cdc_add_job @job_type = 'capture' EXEC sys.sp_cdc_add_job @job_type = 'cleanup', @start_job = 0, @retention = 1440 --Retention period is in minutes EXECUTE sys.sp_cdc_change_job @job_type = 'capture', @maxscans = 1000, @maxtrans = 15 --Update the job parameters EXEC sys.sp_cdc_help_jobs --List CDC jobs EXEC sys.sp_cdc_start_job --Starts the capture job EXEC sys.sp_cdc_stop_job --Stops the capture job EXEC sys.sp_cdc_drop_job @job_type = 'capture' --Drop the CDC Capture job EXECUTE sys.sp_cdc_get_captured_columns @capture_instance = 'dbo_Table1' EXECUTE sys.sp_cdc_get_ddl_history @capture_instance = 'dbo_Table1' -- 62. Enable Change Tracking on the database ALTER DATABASE Database1 SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON) -- 63. Disable Change Tracking on the database ALTER DATABASE Database1 SET CHANGE_TRACKING = OFF -- 64. Enable Change Tracking on a table ALTER TABLE dbo.Table1 ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON) -- 65. Disable Change Tracking on a table ALTER TABLE dbo.Table1 DISABLE CHANGE_TRACKING -- 66. Change Tracking functions SELECT CHANGE_TRACKING_CURRENT_VERSION() SELECT CT.Column1, T1.Column2, T1.Column3, CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT FROM dbo.Table1 AS T1 RIGHT JOIN CHANGETABLE(CHANGES dbo.Table1, 0) AS CT --Change 0 with the output of CHANGE_TRACKING_CURRENT_VERSION(). CHANGETABLE must be aliased. ON T1.Column1 = CT.Column1 -- The PK of Table1 -- 67. Create a trigger CREATE TRIGGER Trigger1 ON dbo.Table1 AFTER INSERT, UPDATE, DELETE AS INSERT INTO Table1_Audit SELECT * FROM Inserted -- 68. Delete a trigger DROP TRIGGER Trigger1 -- 69. Disable a trigger DISABLE TRIGGER Trigger1 on dbo.Table1 -- 70. Enable a trigger ENABLE TRIGGER Trigger1 on dbo.Table1 -- PART 2. DATABASE DEVELOPMENT -- 1. Select rows SELECT Column1, Column2 FROM Table1 WHERE Column1 = 3 -- 2. Update rows UPDATE Table1 SET Column2 = 'A' WHERE Column1 = 3 -- 3. Insert rows INSERT INTO Table1 (Column1, Column2) VALUES (1,'A'), (2,'B'), (3,'C') -- 4. Delete rows DELETE FROM Table1 WHERE Column1 = 3 DELETE TOP(1000) FROM Table1 WHERE Column1 < '31 Dec 2015' -- 5. Grouping rows SELECT Column2, sum(Column3) FROM Table1 GROUP BY Column2 ORDER BY Column2 Desc -- 6. Inner join SELECT T1.Column1, T1.Column2, T2.Column2 FROM Table1 T1 INNER JOIN Table2 T2 on T1.Column1 = T2.Column1 --Only include rows exist in both tables -- 7. Left and right join SELECT T1.Column1, T1.Column2, T2.Column2, T3.Column3 FROM Table1 T1 LEFT JOIN Table2 T2 on T1.Column1 = T2.Column1 --Only include rows exists in Table1 RIGHT JOIN Table3 T3 on T1.Column1 = T3.Column1 --Only inlcude rows exists in Table3 -- 8. Full outer join SELECT T1.Column1, T1.Column2, T2.Column2 FROM Table1 T1 FULL OUTER JOIN Table2 T2 on T1.Column1 = T2.Column1 --Include all rows from Table1, and all rows from Table2 -- 9. Join hint SELECT T1.Column1, T1.Column2, T2.Column2 FROM Table1 T1 INNER HASH JOIN Table2 T2 on T1.Column1 = T2.Column1 --Forces SQL Server to use Hash Join (good for large table) SELECT T1.Column1, T1.Column2, T2.Column2 FROM Table1 T1 INNER JOIN Table2 T2 on T1.Column1 = T2.Column1 OPTION (HASH JOIN) --Alternative way of forcing Hash join SELECT T1.Column1, T1.Column2, T2.Column2 FROM Table1 T1 INNER MERGE JOIN Table2 T2 on T1.Column1 = T2.Column1 --Forces SQL Server to use Merge Join (good for large table which is sorted) SELECT T1.Column1, T1.Column2, T2.Column2 FROM Table1 T1 INNER JOIN Table2 T2 on T1.Column1 = T2.Column1 OPTION (MERGE JOIN) --Alternative way of forcing Merge join SELECT T1.Column1, T1.Column2, T2.Column2 FROM Table1 T1 INNER LOOP JOIN Table2 T2 on T1.Column1 = T2.Column1 --Forces SQL Server to use Loop Join (good for small tables) SELECT T1.Column1, T1.Column2, T2.Column2 FROM Table1 T1 INNER JOIN Table2 T2 on T1.Column1 = T2.Column1 OPTION (LOOP JOIN) --Alternative way of forcing Loop join -- 10. Like operator SELECT Column1, Column2 FROM Table1 WHERE Column2 LIKE '%MOTOR%' -- 11. Collation SELECT T1.Column1, T2.Column2 FROM DB1.dbo.Table1 T1 JOIN DB2.dbo.Table2 T2 ON T1.Column1 COLLATE SQL_Latin1_General_CP1_CI_AS = T2.Column1 --Joining tables from different DBs with different collation -- 12. Correlated subquery (a subquery which depends on the outer query) SELECT * FROM Table1 T1 WHERE NOT EXISTS (SELECT T1.Column1 FROM Table2 T2 WHERE T1.Column1 = T2.Column1) -- 13. Select into SELECT * INTO Table2 FROM Table1 WHERE Column1 = 2 --Table2 will be created if it doesn't exist. If it exists we get an error. -- 14. Insert Select INSERT INTO Table2 (Column1, Column2) SELECT Column1, Column2 FROM Table1 -- 14a. Output an SP into a table sp_configure 'show advanced options', 1; --Ad Hoc Distributed Queries is an advanced option. We need to set Show Advanced Option to Yes so we can see the Ad Hoc Distributed Queries option. RECONFIGURE; sp_configure 'Ad Hoc Distributed Queries', 1; --To set SQL Server to allow ad hoc access to distributed query RECONFIGURE; SELECT * INTO #Temp FROM OPENROWSET('SQLNCLI', 'Server=Server1;Trusted_Connection=yes;', 'EXEC Database1.dbo.SP1') --OpenRowSet uses Ad Hoc distributed query -- 15. Update from UPDATE Table1 SET Column2 = T2.Column2 FROM Table1 T1 JOIN Table2 T2 on T1.Column1 = T2.Column1 WHERE T1.Column3 = 'A' -- 16. Delete from DELETE T1 FROM Table1 T1 JOIN Table2 T2 on T1.Column1 = T2.Column1 WHERE T2.Column2 = 'A' -- 17. Truncate table TRUNCATE TABLE Table1 -- 18. Cast SELECT cast(Column1 as Varchar) FROM Table1 -- 19. Convert SELECT convert(Varchar, Column1) FROM Table1 SELECT convert(Varchar(50),NULL) FROM Table1 --To set the data type of a temp table column SELECT convert(Varchar,DateColumn,120) FROM Table1 --We can't specify the Style using Cast (120 means YYYY-MM-DD hh:mi:ss) -- 20. NULLIF SELECT 1/nullif(Column1,0) FROM Table1 --To avoid division by 0 -- 21. ISNULL SELECT isnull(Column1,0) FROM Table1 --Change NULL with 0 -- 22. Coalesce SELECT coalesce(col1, col2, col3) FROM Table1 --Returns the first not null column -- 23. Union and union all SELECT Col1, Col2 FROM Table1 UNION SELECT Col1, Col2 FROM Table2 --Rows with same values are only displayed once. Rows with all NULL are displayed. SELECT Col1, Col2 FROM Table1 UNION SELECT Col1, Col2 FROM Table2 --Rows with same values are only displayed many times. Rows with all NULL are displayed. -- 24. Declare a variable DECLARE @Var1 Varchar(20) = 'String1' SELECT @Var1 -- 25. Create a temp table IF object_id('TempDB..#TempTable1') is not null DROP TABLE #TempTable1 SELECT Column1 as NewColumn1, Convert(Varchar(50),Column2) as NewColumn2 INTO #TempTable1 FROM Table1 -- To dictate the data type of NewColumn2 -- Or specify columns and data types: IF object_id('TempDB..#TempTable1') is not null DROP TABLE #TempTable1 CREATE TABLE #TempTable1 ( Column1 Int, Column2 Varchar(20) ) -- 26. Drop a temp table IF object_id('TempDB..#TempTable1') is not null DROP TABLE #TempTable1 -- 27. Create global temp table IF object_id('TempDB..##TempTable1') is not null DROP TABLE ##TempTable1 SELECT Col1, Col2 INTO ##TempTable1 FROM Table1 -- Or specify columns and data types: IF object_id('TempDB..##TempTable1') is not null DROP TABLE ##TempTable1 CREATE TABLE ##TempTable1 ( Col1 Int, Col2 Varchar(20) ) -- 28. Drop global temp table IF object_id('TempDB..##TempTable1') is not null DROP TABLE ##TempTable1 -- 29. Create a table variable DECLARE @Var1 TABLE ( Column1 Int, Column2 Varchar(20) ) INSERT INTO @Var1 (Column1, Column2) VALUES (1,2) SELECT * FROM @Var1 -- 30. Memory-optimized table variables (2014) CREATE TYPE dbo.Table1 AS TABLE ( Column1 Int NOT NULL, Column2 Varchar(20) NOT NULL, INDEX Index1 HASH (Column1) WITH (BUCKET_COUNT = 128) ) WITH (MEMORY_OPTIMIZED = ON) DECLARE @Var1 dbo.Table1 INSERT @Var1 (Column1, Column2) VALUES (1,2) SELECT * FROM @Var1 -- 31. Looping on a table variable DECLARE @RowCount Int, @LoopCount Int, @Column1 Int, @Column2 Varchar(20) SELECT @RowCount = Count(*) FROM @Var1 SET @LoopCount = 1 WHILE @LoopCount <= @RowCount BEGIN SELECT TOP 1 @Column1=Column1, @Column2=Column2 FROM @Var1 ORDER BY Column1 SELECT @Column1, @Column2 SET @LoopCount = @LoopCount + 1 DELETE FROM @Var1 WHERE Column1 = @Column1 END -- 32. Delete from a table variable DELETE FROM @Var1