SQL Server Query Archive

Foreign key index check - 2000

2007-07-05T13:55:00.000+02:00

select object_name(constid) FKName,
object_name(fk.fkeyid) TableName,
sc.name ColumnName,
object_name(fk.rkeyid) ReferenceTableName,
sc2.name ReferenceColumnName
from sysforeignkeys fk
join syscolumns sc on sc.id = fk.fkeyid and sc.colid = fk.fkey
join syscolumns sc2 on sc2.id = fk.rkeyid and sc2.colid = fk.rkey
where not exists
(
select *
from sysindexkeys sik
where sik.id = fk.fkeyid
and sik.colid = sc.colid
and sik.keyno = 1 -- first column in the index
)
order by TableName, ReferenceTableName

Drop all statistics - 2005

2007-04-03T11:40:00.001+02:00

DECLARE @ObjectName sysname
DECLARE @StatsName sysname
DECLARE StatsCursor CURSOR FAST_FORWARD
FOR
SELECT OBJECT_NAME(object_id) as 'ObjectName', [name] as 'StatsName' FROM sys.stats
WHERE (INDEXPROPERTY(object_id, [name], 'IsAutoStatistics') = 1 OR INDEXPROPERTY(object_id, [name], 'IsStatistics') = 1)
AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0
OPEN StatsCursor
FETCH NEXT FROM StatsCursor
INTO @ObjectName, @StatsName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('DROP STATISTICS ' + @ObjectName + '.' + @StatsName)
FETCH NEXT FROM StatsCursor
INTO @ObjectName, @StatsName
END
CLOSE StatsCursor
DEALLOCATE StatsCursor

Get space used per file - 2000

2007-03-05T09:36:00.000+01:00

SELECT name,
CAST( ( FILEPROPERTY( name, 'SpaceUsed' ) * 8 )
/ 1024. AS numeric( 10, 2 ) ) as 'SpaceUsed',
CAST( ( size * 8 ) / 1024. AS numeric( 10, 2 ) ) as 'Reserved'
FROM sysfiles

The real SQL Server query archive

2006-11-27T09:42:00.000+01:00

Microsoft copied our initiative :-)

Check it out here

Space used - 2000

2006-11-24T18:34:00.000+01:00

-- DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS;

declare AllTables cursor fast_forward for
select name from sysobjects where type='u'

open AllTables

declare @TableName varchar(255)
create table #SpaceUsed
(
TableName varchar(255),
NbrRows varchar(255),
Reserved varchar(255),
Data varchar(255),
Index_Size varchar(255),
[Free] varchar(255)
)

fetch from AllTables into @TableName
while @@FETCH_STATUS=0
begin
insert into #SpaceUsed
exec sp_spaceused @TableName
fetch next from AllTables into @TableName
end

close AllTables
deallocate AllTables

select * from #SpaceUsed order by TableName

drop table #SpaceUsed

Calculate waitstats - 2005

2006-10-09T12:54:00.000+02:00

WITH TotalCTE (wait_type, wait_time_ms, waiting_tasks_count) AS
(
SELECT wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('SLEEP', 'RESOURCE_QUEUE', 'OLEDB','WAITFOR', 'SQLTRACE_BUFFER_FLUSH', 'SLEEP_TASK', 'LAZYWRITER_SLEEP', 'MISCELLANEOUS')
),
TotalWait (TotalWaitMs) AS
(
SELECT SUM(wait_time_ms) FROM TotalCTE
)
SELECT wait_type, (wait_time_ms * 1. / TotalWaitMs ) * 100 as 'Percentage', wait_time_ms / 1000 as WaitTimeInSecs, wait_time_ms / (waiting_tasks_count + .001) as AvgWaitTime, waiting_tasks_count
FROM TotalCTE, TotalWait
ORDER BY Percentage DESC

Foreign key DROP/CREATE generator - 2005

2006-09-07T21:32:00.000+02:00

IF OBJECT_ID (N'dbo.GetFieldsConcat', N'FN') IS NOT NULL
DROP FUNCTION dbo.GetFieldsConcat;
GO
CREATE FUNCTION dbo.GetFieldsConcat (@objectName sysname, @foreignKeyName sysname, @isParent bit)
RETURNS varchar(max)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @theFields varchar(max)
SET @theFields = ''
SELECT @theFields = @theFields + '[' + CASE WHEN @isParent = 1 THEN pc.name ELSE cc.name END + '],'
FROM sys.foreign_keys fk
INNER JOIN sys.schemas fks ON fks.schema_id = fk.schema_id
INNER JOIN sys.objects oc ON fk.parent_object_id = oc.object_id
INNER JOIN sys.schemas cs ON cs.schema_id = oc.schema_id
INNER JOIN sys.objects op ON fk.referenced_object_id = op.object_id
INNER JOIN sys.schemas ps ON ps.schema_id = op.schema_id
INNER JOIN sys.foreign_key_columns pfkc ON pfkc.constraint_object_id = fk.object_id
INNER JOIN sys.columns pc ON pc.column_id = pfkc.referenced_column_id AND pc.object_id = fk.referenced_object_id
INNER JOIN sys.columns cc ON cc.column_id = pfkc.parent_column_id AND cc.object_id = fk.parent_object_id
WHERE ((@isParent = 1 AND op.name = @objectName) OR (@isParent = 0 AND oc.name = @objectName))
AND fk.name = @foreignKeyName
IF LEN(@theFields) > 0
SET @theFields = LEFT(@theFields, LEN(@theFields) - 1)
RETURN(@theFields)
END;
GO

DECLARE @TableName sysname
SET @TableName = 'tbl_stockable_type'
SELECT 'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[' + fks.name + '].[' + fk.name + ']'') AND parent_object_id = OBJECT_ID(N''[' + cs.name + '].[' + oc.name + ']''))' + CHAR(13) + CHAR(10) +
'ALTER TABLE [' + cs.name + '].[' + oc.name + '] DROP CONSTRAINT [' + fk.name + ']'
FROM sys.foreign_keys fk
INNER JOIN sys.schemas fks ON fks.schema_id = fk.schema_id
INNER JOIN sys.objects oc ON fk.parent_object_id = oc.object_id
INNER JOIN sys.objects op ON fk.referenced_object_id = op.object_id
INNER JOIN sys.schemas cs ON cs.schema_id = oc.schema_id
WHERE op.name = @TableName
UNION ALL
SELECT 'ALTER TABLE [' + cs.name + '].[' + oc.name + '] WITH NOCHECK ADD CONSTRAINT [' + fk.name + '] FOREIGN KEY(' + dbo.GetFieldsConcat(oc.name, fk.name, 0) + ')' + CHAR(13) + CHAR(10)
+ 'REFERENCES [' + ps.name + '].[' + op.name + '] (' + dbo.GetFieldsConcat(op.name, fk.name, 1) + ')'
FROM sys.foreign_keys fk
INNER JOIN sys.schemas fks ON fks.schema_id = fk.schema_id
INNER JOIN sys.objects oc ON fk.parent_object_id = oc.object_id
INNER JOIN sys.schemas cs ON cs.schema_id = oc.schema_id
INNER JOIN sys.objects op ON fk.referenced_object_id = op.object_id
INNER JOIN sys.schemas ps ON ps.schema_id = op.schema_id
WHERE op.name = @TableName

Foreign key index check - 2005

2006-09-07T21:29:00.000+02:00

SELECT fk.Name as 'ForeignKey', OBJECT_NAME(fk.parent_object_id) as 'ChildTable',
OBJECT_NAME(fk.referenced_object_id) as 'ParentTable', c.Name as 'Column', i.Name as 'ValidIndex'
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c ON c.object_id = fk.parent_object_id AND c.column_id = fkc.parent_column_id
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = fk.parent_object_id AND c.column_id = ic.column_id AND ic.key_ordinal = 1
LEFT OUTER JOIN sys.indexes i ON i.object_id = ic.object_id AND i.index_id = ic.index_id
--WHERE i.Name IS NULL
ORDER BY ChildTable, ParentTable

Welcome

2006-09-07T10:13:00.000+02:00

killspid and I decided to gather all our much used queries on a single location. A great addition to our long term memory :-)