Sql Server Query collection

Autoclose.sql

--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'
 

diagnostics.sql

--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
 

 
--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)