Check/Disable/Enable FK

SELECT
 (CASE WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED' ELSE 'DISABLED' END) AS STATUS
 ,OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME
 ,OBJECT_NAME(FKEYID) AS TABLE_NAME
 ,COL_NAME(FKEYID, FKEY) AS COLUMN_NAME
 ,OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME
 ,COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
 FROM SYSFOREIGNKEYS
 ORDER BY TABLE_NAME
    , CONSTRAINT_NAME
    , REFERENCED_TABLE_NAME
    , KEYNO


http://www.jasinskionline.com/TechnicalWiki/Disable-Every-Foreign-Key.ashx?AspxAutoDetectCookieSupport=1

For SQL 2005 and above:
-- This SQL generates a set of SQL statements to disable every foreign
-- key in the database
select distinct
    sql = 'ALTER TABLE [' + onSchema.name + '].[' + onTable.name +
            '] NOCHECK CONSTRAINT [' + foreignKey.name + ']'
from sysforeignkeys fk inner join sysobjects foreignKey 
        on foreignKey.id = fk.constid
  inner join sys.objects onTable
        on fk.fkeyid = onTable.object_id
  inner join sys.schemas onSchema
        on onSchema.schema_id = onTable.schema_id
  inner join sysobjects againstTable 
        on fk.rkeyid = againstTable.id
where againstTable.TYPE = 'U'
  and onTable.TYPE = 'U'
  and ObjectProperty(fk.constid,'CnstIsDisabled') = 0
order by 1
-- This SQL generates a set of SQL statements to re-enable every foreign
-- key in the database
select distinct
    sql = 'ALTER TABLE [' + onSchema.name + '].[' + onTable.name +
            '] CHECK CONSTRAINT [' + foreignKey.name + ']'
from sysforeignkeys fk inner join sysobjects foreignKey 
        on foreignKey.id = fk.constid
  inner join sys.objects onTable
        on fk.fkeyid = onTable.object_id
  inner join sys.schemas onSchema
        on onSchema.schema_id = onTable.schema_id
  inner join sysobjects againstTable 
        on fk.rkeyid = againstTable.id
where againstTable.TYPE = 'U'
  and onTable.TYPE = 'U'
  and ObjectProperty(fk.constid,'CnstIsDisabled') = 1
order by 1

Quick way to remove all data from a database (remove FK, truncate and add FK back)

The following script can be used to generate script for these 3 steps which can be used to remove all data from a database quickly and recover FK inclusing check option.


--
-- the following is intresting but has some limitation
-- 1) can not handle the case when FK has multiple columns
-- 2) can not handle the case when FK refers to unique index

SET NOCOUNT ON
 DECLARE @FkDefination TABLE(
  Id INT PRIMARY KEY IDENTITY(1, 1),
  FKConstraintName VARCHAR(255),
  FKConstraintTableSchema VARCHAR(255),
  FKConstraintTableName VARCHAR(255),
  FKConstraintColumnName VARCHAR(255),
  PKConstraintName VARCHAR(255),
  PKConstraintTableSchema VARCHAR(255),
  PKConstraintTableName VARCHAR(255),
  PKConstraintColumnName VARCHAR(255),
  FkIsEnabled TINYINT   
 )
 --
 -- get FK definitions and property
 INSERT INTO @FkDefination(FKConstraintName, FKConstraintTableSchema, FKConstraintTableName, FKConstraintColumnName, FkIsEnabled)
  (
  SELECT
 kcu.CONSTRAINT_NAME
 ,kcu.TABLE_SCHEMA
 ,kcu.TABLE_NAME
 ,kcu.COLUMN_NAME
 ,1-ObjectProperty(fk.constid,'CnstIsDisabled') as FkIsEnabled
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
     INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
     ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
  INNER JOIN sysobjects o on o.name=tc.CONSTRAINT_NAME
  INNER JOIN sysforeignkeys fk
  ON fk.constid=o.id 
   WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
  )
--
-- get PK and unique constrains name
 UPDATE @FkDefination
 SET PKConstraintName = UNIQUE_CONSTRAINT_NAME
     FROM @FkDefination tt
    INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
    ON tt.FKConstraintName = rc.CONSTRAINT_NAME
--
-- get reference schema and table name
-- this seems to be working with unique constraint and PK, but not unique index?
 UPDATE @FkDefination
 SET PKConstraintTableSchema  = TABLE_SCHEMA,
   PKConstraintTableName  = TABLE_NAME
  FROM @FkDefination fkd INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    ON fkd.PKConstraintName = tc.CONSTRAINT_NAME
--
-- get reference column name
 UPDATE @FkDefination
 SET PKConstraintColumnName = COLUMN_NAME
  FROM @FkDefination fkd
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON fkd.PKConstraintName = kcu.CONSTRAINT_NAME
--select * from @FkDefination
 -- step 1: drop constraint:
 SELECT
        'ALTER TABLE [' + FKConstraintTableSchema + '].[' + FKConstraintTableName + ']  DROP CONSTRAINT ' + FKConstraintName AS DropFkStatement
     FROM @FkDefination
 -- step 2: truncate tables
 SELECT 'TRUNCATE TABLE '+s.name+'.'+t.name as 'TruncateStatement'
   FROM sys.tables t inner join sys.schemas s
  on t.schema_id=s.schema_id
  WHERE t.type='U'
  ORDER BY s.name, t.name
 -- step 3:
 -- add fk back
 SELECT
        'ALTER TABLE [' + FKConstraintTableSchema + '].[' + FKConstraintTableName + '] WITH '+CASE WHEN FkIsEnabled=1 THEN 'CHECK' ELSE 'NOCHECK' END +' ADD CONSTRAINT ' + FKConstraintName + ' FOREIGN KEY(' + FKConstraintColumnName + ') REFERENCES [' + PKConstraintTableSchema + '].[' + PKConstraintTableName + '](' + PKConstraintColumnName + ')' AS CreateFkStatement
     FROM @FkDefination
  WHERE PKConstraintTableName IS NOT NULL



reference:
http://stackoverflow.com/questions/11639868/temporarily-disable-all-foreign-key-constraints

Tempdb and space usage analysis

ref:
http://thesqldude.com/2012/05/15/monitoring-tempdb-space-usage-and-scripts-for-finding-queries-which-are-using-excessive-tempdb-space/

1) Which kind of objects consume more space in tempdb?

SELECT
SUM (user_object_reserved_page_count)*8 as user_obj_kb,
SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
SUM (version_store_reserved_page_count)*8  as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb,
SUM (mixed_extent_page_count)*8 as mixedextent_kb
FROM sys.dm_db_file_space_usage

 2) Which active scripts consume most space?

SELECT es.host_name , es.login_name , es.program_name,
st.dbid as QueryExecContextDBID, DB_NAME(st.dbid) as QueryExecContextDBNAME, st.objectid as ModuleObjectId,
SUBSTRING(st.text, er.statement_start_offset/2 + 1,(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),st.text)) * 2 ELSE er.statement_end_offset 
END - er.statement_start_offset)/2) as Query_Text,
tsu.session_id ,tsu.request_id, tsu.exec_context_id, 
(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) as OutStanding_user_objects_page_counts,
(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) as OutStanding_internal_objects_page_counts,
er.start_time, er.command, er.open_transaction_count, er.percent_complete, er.estimated_completion_time, er.cpu_time, er.total_elapsed_time, er.reads,er.writes, er.logical_reads, er.granted_query_memory
FROM sys.dm_db_task_space_usage tsu inner join sys.dm_exec_requests er 
 ON ( tsu.session_id = er.session_id and tsu.request_id = er.request_id) 
inner join sys.dm_exec_sessions es ON ( tsu.session_id = es.session_id ) 
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE (tsu.internal_objects_alloc_page_count+tsu.user_objects_alloc_page_count) > 0
ORDER BY (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count)+(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) DESC

3) Longest transactions taking temp space

 SELECT top 5 a.session_id, a.transaction_id, a.transaction_sequence_num, a.elapsed_time_seconds, b.program_name, b.open_tran, b.status FROM sys.dm_tran_active_snapshot_database_transactions a join sys.sysprocesses b on a.session_id = b.spid ORDER BY elapsed_time_seconds DESC

4) Checking number of files:

Declare @tempdbfilecount as int;
select @tempdbfilecount = (select count(*) from sys.master_files where database_id=2 and type=0);
WITH Processor_CTE ([cpu_count], [hyperthread_ratio])
AS
(
      SELECT  cpu_count, hyperthread_ratio
      FROM sys.dm_os_sys_info sysinfo
)
select Processor_CTE.cpu_count as [# of Logical Processors], @tempdbfilecount as [Current_Tempdb_DataFileCount], 
(case 
      when (cpu_count<8 and @tempdbfilecount=cpu_count)  then 'No' 
      when (cpu_count<8 and @tempdbfilecount<>cpu_count and @tempdbfilecount<cpu_count) then 'Yes' 
      when (cpu_count<8 and @tempdbfilecount<>cpu_count and @tempdbfilecount>cpu_count) then 'No'
      when (cpu_count>=8 and @tempdbfilecount=cpu_count)  then 'No (Depends on continued Contention)' 
      when (cpu_count>=8 and @tempdbfilecount<>cpu_count and @tempdbfilecount<cpu_count) then 'Yes'
      when (cpu_count>=8 and @tempdbfilecount<>cpu_count and @tempdbfilecount>cpu_count) then 'No (Depends on continued Contention)'
end) AS [TempDB_DataFileCount_ChangeRequired]
from Processor_CTE;

 

5) Active session space consummation stats

SELECT
  sys.dm_exec_sessions.session_id AS [SESSION ID]
  ,DB_NAME(database_id) AS [DATABASE Name]
  ,HOST_NAME AS [System Name]
  ,program_name AS [Program Name]
  ,login_name AS [USER Name]
  ,status
  ,cpu_time AS [CPU TIME (in milisec)]
  ,total_scheduled_time AS [Total Scheduled TIME (in milisec)]
  ,total_elapsed_time AS    [Elapsed TIME (in milisec)]
  ,(memory_usage * 8)      AS [Memory USAGE (in KB)]
  ,(user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)]
  ,(user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)]
  ,(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)]
  ,(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)]
  ,CASE is_user_process
             WHEN 1      THEN 'user session'
             WHEN 0      THEN 'system session'
  END         AS [SESSION Type], row_count AS [ROW COUNT]
FROM 
  sys.dm_db_session_space_usage
INNER join
  sys.dm_exec_sessions
ON  sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id

Checking database file free space

 select
       name
     , filename
     , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
     , convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
     , convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB
     from dbo.sysfiles a

Find all (and/or system named) default constraint from a given database

USE [DatabaseName]
GO

--
-- find all system named default constraint
select
  t.name TABLE_NAME
  , c.name COLUMN_NAME
  , d.name CONSTRAINT_NAME
  , cm.text DEFAULT_VALUE
  , 'ALTER TABLE [dbo].['+t.name +'] DROP CONSTRAINT [' +d.name +']' AS DROP_STATEMENT
  , 'ALTER TABLE [dbo].['+t.name+'] ADD  CONSTRAINT ['+d.name+']  DEFAULT '+cm.text+' FOR ['+c.name+']' as CREATE_STATEMENT
  , 'ECHO ALTER TABLE [dbo].['+t.name+'] ADD  CONSTRAINT ['+d.name+']  DEFAULT '+cm.text+' FOR ['+c.name+'] > ' +  d.name+'.defconst.sql' as CREATE_BATCH_COMMAND
 from sys.default_constraints d
  inner join sys.tables t
   on t.object_id = d.parent_object_id
   inner join sys.columns c
   on c.object_id = t.object_id and c.column_id = d.parent_column_id
   inner join sys.syscomments cm
  on cm.id=d.object_id
 where d.is_system_named=1 and t.is_ms_shipped=0
 order by 1,2




GO

 SELECT 
 b.name AS TABLE_NAME,
 d.name AS COLUMN_NAME,
 a.name AS CONSTRAINT_NAME,
 c.text AS DEFAULT_VALUE
 ,a.*
 FROM sys.sysobjects a
   INNER JOIN
    (SELECT name, id
     FROM sys.sysobjects
     WHERE xtype = 'U') b
   ON (a.parent_obj = b.id)
   INNER JOIN sys.syscomments c
   ON (a.id = c.id)
            INNER JOIN sys.syscolumns d ON (d.cdefault = a.id)                                         
  WHERE a.xtype = 'D'       
  ORDER BY b.name, a.name

Check SQL Server Table/Index Space

select top 50
    -- one page is 8K
    sum(s.page_count)/125.00 as [Size(MB)]
    -- record_count is at index level, can not do total sum here
    -- also using limited level with dm_db_index_physical_stats will give us null value
    --,sum(isnull(s.record_count, 0)) as total_record_Count
    ,d.name as DataBaseName
    ,sc.name as SchemaName
    ,o.name as TableName
    ,i.type_desc as IndexType
    ,i.name as IndexName
from    --sys.dm_db_index_physical_stats(db_id(), null, null, null, 'detailed') s
        sys.dm_db_index_physical_stats(db_id(), null, null, null, 'limited') s
        inner join sys.databases d on d.database_id=s.database_id
        inner join sys.objects o on o.object_id=s.object_id
        left join sys.indexes i on i.object_id=s.object_id and i.index_id=s.index_id
        inner join sys.schemas sc on sc.schema_id=o.schema_id
where    -- specfic index type (0 : heap, 1: clustered, 2: non-clusterd)
        i.type in (0,1,2)
        --and i.type in (0,1)
group by d.name, sc.name, o.name, i.name, i.type_desc
order by 1 desc
--go
--sp_spaceused 'MyTableName', @updateusage = N'TRUE'

Sql Server Table Row Count

SELECT
 s.Name as SchemaName
 ,t.NAME AS TableName
 ,sum(p.rows) as RowCounts
    FROM sys.tables t INNER JOIN sys.indexes i
   ON t.OBJECT_ID = i.object_id
   INNER JOIN sys.partitions p
   ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
   INNER JOIN sys.schemas s
   ON s.schema_id=t.schema_id
 WHERE -- heap or clustered
   i.type IN (0, 1)
 GROUP BY s.Name, t.NAME, i.object_id, i.index_id, i.name
 -- use this to check list empty table ONLY
 --HAVING SUM(p.rows) = 0
 order by 1,2