Last active
December 3, 2015 11:08
-
-
Save giammin/0b0833e08ea36e9ca29e to your computer and use it in GitHub Desktop.
Sql Server Query collection
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--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' | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--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) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment