Life with SQL server

  --Creates an 'InLine' Table Valued Function (TVF) -- Author: krishang -- Date: 28 Feb -2011 -- Remark: this function is similar to the uIntIDTotable however takes a string and returns a table with the string value -- sperated by the delimiter. also includes an index which for the elements. create FUNCTION [dbo].[uSplit] ( @Delimiter varchar(5), @List varchar(max) ) RETURNS @TableOfValues table ( RowID smallint IDENTITY(1,1), [Value] varchar(max) ) AS BEGIN DECLARE @LenString int WHILE len( @List ) > 0 BEGIN SELECT @LenString = (CASE charindex( @Delimiter, @List ) WHEN 0 THEN len( @List ) ELSE ( charindex( @Delimiter, @List ) -1 ) END ) INSERT INTO @TableOfValues SELECT substring( @List, 1, @LenString ) ...

/* create function */ CREATE FUNCTION [dbo].[IntIDToTable] (@vc_Ids varchar(1000), @vc_sperator char(1) ) -- Author Krishan Gurusinghe -- Remark : This function takes a list if indexes in the form of a string variable and then returns -- a table data type variable used for a select * from D in (select ID from intIDToTable(@d) -- RETURNS @IDTable TABLE (Id numeric(18,0)) BEGIN DECLARE @in_Index1 AS INT, --Used to store EmployeeID delimiter(',') position in string @vc_AnyId AS VARCHAR(15) /* initialize working variables */ SET @in_Index1 = CHARINDEX(@vc_sperator,@vc_Ids) /* loop through facility ids in delimited string */ WHILE (@in_Index1 > 0 OR LEN(@vc_Ids) > 0) BEGIN /* parse out single facility id for processing */ IF @in_Index1 > 0 BEGIN SET @vc_AnyId = Left(@vc_Ids,@in_Index1 - 1) SET @vc_Ids = Right(@vc_Ids,Len(@vc_Ids) - @in_Index1) END ELSE BEGIN SET @vc_AnyId = @vc_Ids SET @vc_...

Figure out what tables have records in them. The following gives a summary. SELECT QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName] , SUM(sPTN.Rows) AS [RowCount] into utblListOftables FROM sys.objects AS sOBJ INNER JOIN sys.partitions AS sPTN ON sOBJ.object_id = sPTN.object_id WHERE sOBJ.type = 'U' AND sOBJ.is_ms_shipped = 0x0 AND index_id < 2 -- 0:Heap, 1:Clustered GROUP BY sOBJ.schema_id , sOBJ.name ORDER BY [TableName]; select * from utblListOftables The following gives the utilzation and update stats Select Object_Name(ix.[object_id]) as objectName , Sum(ddius.user_seeks) As 'table_seeks' , Sum(ddius.user_scans) As 'table_scans' , Sum(ddius.user_lookups) As 'table_lookups' , Sum(ddius.user_updates) As 'table_updates' , Sum(ddius.user_seeks + ddius.user_scans) As 'query...