| Jørgen's profileGuldmann Fumbles with Ma...PhotosBlogLists |  Tools  Help |
|
|
The being witty remark trickWhen creating tables I often have a drop section in the top of the script which is remarked in and out when needed.
/*
DROP TABLE [dbo].[CrappyTable] */ CREATE TABLE [dbo].[CrappyTable] (ID_CrappyTable INT IDENTITY (1,1) PRIMARY KEY) By using my remarks slightly different it enables me to quickly comment the section in. /*
DROP TABLE [dbo].[CrappyTable] --*/ So when enabling the section I simply … --/*
DROP TABLE [dbo].[CrappyTable] --*/ FieldUseHow often have you not been a situation where you needed to reverse engineer the logical structure of an unknown database or needed to track down some data and how the relate. CREATE PROCEDURE [dbo].[FieldUse]@field sysname, @value sysname AS BEGIN DECLARE @SQL NVARCHAR(MAX) SET @SQL='' SELECT @SQL=@SQL+'SELECT '''+s.NAME+''' AS [Schema],'''+t.NAME+''' AS [Table], '''+COALESCE([collation_name],'')+''' AS [collation_name] ' +',COUNT(*) AS Occurrences FROM ['+s.[NAME]+'].['+t.NAME+'] WHERE ['+@field+']='''+@value+''';' FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE c.name = @field ORDER BY s.name ,t.NAME; EXEC sp_executesql @SQL END Retrieve column info from SYSSELECT tschema.[name] AS SchemaName ,ttables.[name] AS TableName ,ttypes.[name] AS DataTypeName ,ttypes.[max_length] ,ttypes.[precision] ,ttypes.[scale] FROM sys.syscolumns tsyscolumns INNER JOIN sys.tables ttables ON tsyscolumns.id = ttables.[object_id] INNER JOIN sys.schemas tschema ON ttables.schema_id = tschema.schema_id INNER JOIN sys.types ttypes ON tsyscolumns.xtype=ttypes.system_type_id programmatically determine the number of processors the box hasEXEC master..xp_msver N'ProcessorCount', N'ProcessorType' Finding tables with no identity columns or alternate keysSET NOCOUNT ON
SELECT 'Tables without an identity column and primary key'
SELECT schema_name(schema_id) + '.' + [name] AS tableName
FROM sys.tables AS t WHERE type_desc = 'USER_TABLE' AND (NOT EXISTS (SELECT * FROM sys.columns AS c WHERE t.object_id = c.object_id AND is_identity = 1) OR NOT EXISTS (SELECT * FROM sys.objects o WHERE t.object_id = o.parent_object_id AND type_desc = 'PRIMARY_KEY_CONSTRAINT')) SELECT 'Tables with no UNIQUE CONSTRAINT to back up the alternate key' SELECT schema_name(schema_id) + '.' + [name] AS tableName FROM sys.tables AS t WHERE type_desc = 'USER_TABLE' AND NOT EXISTS (SELECT * FROM sys.objects o WHERE t.object_id = o.parent_object_id AND type_desc = 'UNIQUE_CONSTRAINT') Show all the indexes that have been used in a given databaseThis query shows all the indexes that have been used for a given database
SELECT OBJECT_NAME(s.OBJECT_ID) AS ObjectName , s.OBJECT_ID , i.[name] AS IndexName , i.index_id , user_seeks , user_scans , user_lookups , user_updates FROM sys.dm_db_index_usage_stats s JOIN sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id WHERE database_id = DB_ID() AND OBJECTPROPERTY(s.OBJECT_ID , 'IsUserTable') = 1 ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC show the average CPU time for the top 5 query execution plans in cache.Dynamic Management Functions to show the average CPU time for the top 5 query execution plans in cache.
SELECT TOP 5 total_worker_time/execution_count [Avg CPU Time] , SUBSTRING(st.[text], (qs.statement_start_offset/2)+1 , ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.[text]) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) statement_text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st ORDER BY total_worker_time/execution_count DESC; Display the amount of used transaction log space in the current databasesUsing dynamic management views it is possible to get at the amount of log space used. In the example below "sys.dm_os_performance_counters" is used to select amount of used transaction log space in the current database
SELECT instance_name
,cntr_value 'Log File(s) Used Size (KB)'
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Log File(s) Used Size (KB)'How to gain Exclusive Access to a databaseShutting out NON ADMINS is very easy ALTER DATABASE [DATABASENAME]
SET single_user WITH ROLLBACK immediate Shutting out ADMINS
DECLARE @spid varchar(10)
SELECT @spid = spid
FROM master.sys.sysprocesses
WHERE dbid = DB_ID('DATABASENAME')
WHILE @@ROWCOUNT <> 0 BEGIN
EXEC('KILL ' + @spid)
SELECT @spid = spid
FROM master.sys.sysprocesses
WHERE dbid =DB_ID('DATABASENAME')
AND spid > @spid
END View where collections differ from the database's default collctionUnintentional Differences in collection decisions within a database can be a aggravating matter. Regaining control and overview can be almost impossible had it not been for this view that exposes all items that varies from the default collection of the database.
CREATE VIEW [Dba].[ShowAttributesWithWrongCollation]
AS
SELECT TABLE_SCHEMA
, TABLE_NAME
, COLUMN_NAME
, DATA_TYPE
, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE (TABLE_NAME IN (SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE = 'Base Table')))
AND (COLLATION_NAME NOT LIKE (SELECT CAST(DATABASEPROPERTYEX('EDW_AMS_DEV', 'Collation') AS VARCHAR(MAX)) AS Expr1)) |
|
|
