Here is a nifty query that will display all the tables and columns set as IDENTITY in a given database:
|
1 2 3 4 5 6 7 8 9 10 11 |
USE AdventureWorks2012 GO SELECT OBJECT_NAME(object_id) AS 'table_name', name AS 'column_name', seed_value, increment_value, last_value, is_not_for_replication FROM sys.identity_columns ORDER BY 1 |
Views –…
Read More
Here is a good blog post on SQL Server coding and naming conventions: Database coding conventions, best practices, programming guidelines Views – 2925
Read More
Here is one way to take a database offline. This will prevent any reads, writes or even a connection to it (including any…
Read More
Here is some simple T-SQL to enable or disable a SQL login account:
|
1 2 3 4 5 6 7 |
-- Disable the login ALTER LOGIN [someLogin] DISABLE GO -- Enable the login ALTER LOGIN [someLogin] ENABLE GO |
Views – 1776
Read More
Here is a segment of code you should add to any T-SQL that is dropping a function or stored procedure to make sure…
Read More
sql_handle: is the hash of the original T-SQL source. plan_handle: is the hash of the cached execution plan. The important thing to note…
Read More
Here are the 4 basic steps all queries go through: 1. Parsing: The query is examined by SQL Server to syntax errors, invalid…
Read More
|
1 2 3 4 5 6 7 8 |
SELECT OBJECT_NAME(objectid) ,BlockTime = total_elapsed_time - total_worker_time ,execution_count ,total_logical_reads FROM sys.dm_exec_query_stats qs CROSS apply sys.dm_exec_sql_text(qs.sql_handle) ORDER BY total_elapsed_time - total_worker_time DESC |
Views – 1796
Read More
|
1 2 3 4 |
SELECT DISTINCT objname = object_name(p.object_id) FROM sys.partitions p JOIN sys.dm_tran_locks t1 ON p.hobt_id = t1.resource_associated_entity_id |
Views – 1591
Read More
Posted on June 17, 2009 by Derek Dieter Finding the lead blocker in a chain of blocks is a difficult challenge when using…
Read More