Db diagnostic query collection

--sql error logs
EXEC sys.xp_readerrorlog 0, 1

-- System Stored Procedures list
-- http://msdn.microsoft.com/en-us/library/ms187961.aspx

--server info
EXEC xp_msver

--count dbs
select count(1) from sysdatabases

--Find the active SQL connections
select db_name(dbid) as [Database Name], count(dbid) as [No Of Connections], loginame as [Login Name]
from sys.sysprocesses
where dbid > 0
group by  dbid, loginame


--iterate table or db
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'
exec sp_MSForEachTable


--check and set auto_close
ALTER DATABASE [dbname] SET AUTO_CLOSE Off WITH NO_WAIT
SELECT DATABASEPROPERTYEX('dbname', 'IsAutoClose');
EXEC sp_MSforeachdb 'ALTER DATABASE [?] SET AUTO_CLOSE Off WITH NO_WAIT'


--query constrain enable, disable, delete
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
EXEC sp_msforeachtable "IF((SELECT OBJECTPROPERTY( OBJECT_ID(N'?'), 'TableHasIdentity')) = 1) SET IDENTITY_INSERT ? ON"


EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
EXEC sp_msforeachtable "IF((SELECT OBJECTPROPERTY( OBJECT_ID(N'?'), 'TableHasIdentity')) = 1) SET IDENTITY_INSERT ? OFF"

DECLARE @sql nvarchar(255)
while (select COUNT(CONSTRAINT_NAME) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_SCHEMA = 'SCHEMANAME' and CONSTRAINT_TYPE ='FOREIGN KEY' and CONSTRAINT_CATALOG='DBNAME')>0
Begin
select top 1 @sql='ALTER TABLE ' + CONSTRAINT_CATALOG+'.'+TABLE_SCHEMA + '.'+ TABLE_NAME + ' DROP CONSTRAINT ['+ CONSTRAINT_NAME + ']'
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_SCHEMA = 'SCHEMANAME' and CONSTRAINT_CATALOG='DBNAME' and CONSTRAINT_TYPE ='FOREIGN KEY'
exec sp_executesql @sql
end



--Maximum length of data in every column in a table
/*LEN function */
USE AdventureWorks2008R2

--declare @TableName sysname = 'Employee', @TableSchema sysname = 'HumanResources'
DECLARE @TableName sysname = 'Address', @TableSchema sysname = 'Person'
DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((SELECT
'
UNION ALL
select ' + QUOTENAME(Table_Name,'''') + ' AS Table_Name, ' +
QUOTENAME(Column_Name,'''') + ' AS ColumnName, MAX(LEN(' + QUOTENAME(Column_Name) +
')) as [Max Length], ' + QUOTENAME(C.DATA_TYPE,'''') + ' AS Data_Type, ' +
CAST(COALESCE(C.CHARACTER_MAXIMUM_LENGTH, C.NUMERIC_SCALE,0) AS VARCHAR(10)) +
'  AS Data_Width FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(Table_Name)
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_NAME = @TableName
AND table_schema = @TableSchema
AND DATA_TYPE NOT IN ('text','ntext','XML','HierarchyID','Geometry','Geography')
ORDER BY COLUMN_NAME
FOR XML PATH(''),Type).value('.','varchar(max)'),1,11,'')
--print @SQL
EXECUTE (@SQL)


/*DATALENGTH function*/

USE AdventureWorks2008R2
--declare @TableName sysname = 'Employee', @TableSchema sysname = 'HumanResources'
DECLARE @TableName sysname = 'Address', @TableSchema sysname = 'Person'
DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((SELECT
'
UNION ALL
select ' + QUOTENAME(Table_Name,'''') + ' AS Table_Name, ' +
QUOTENAME(Column_Name,'''') + ' AS ColumnName, MAX(' +
CASE WHEN DATA_TYPE IN ('XML','HierarchyID','Geometry','Geography','text','ntext')
     THEN 'DATALENGTH(' ELSE 'LEN(' END + QUOTENAME(Column_Name) +
')) as [Max Length], ' + QUOTENAME(C.DATA_TYPE,'''') + ' AS Data_Type, ' +
CAST(COALESCE(C.CHARACTER_MAXIMUM_LENGTH, C.NUMERIC_SCALE,0) AS VARCHAR(10)) +
'  AS Data_Width FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(Table_Name)
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_NAME = @TableName
AND table_schema = @TableSchema
--AND DATA_TYPE NOT IN ('XML','HierarchyID','Geometry','Geography')
ORDER BY COLUMN_NAME
FOR XML PATH(''),Type).value('.','varchar(max)'),1,11,'')
--print @SQL
EXECUTE (@SQL)