Obtener el tamaño de todas las tablas de una base de datos en SQL Server

DECLARE @TableName VARCHAR(100)    --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY

--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

--Open the cursor
OPEN tableCursor

--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName

--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
    --Dump the results of the sp_spaceused query to the temp table
    INSERT  #TempTable
        EXEC sp_spaceused @TableName

    --Get the next table name
    FETCH NEXT FROM tableCursor INTO @TableName
END

--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records so we can use the reults
SELECT *
FROM #TempTable
ORDER BY tableName ASC
--Final cleanup!
DROP TABLE #TempTable

GO

Buscar en el contenido de un stored procedure

SELECT Name,OBJECT_DEFINITION(OBJECT_ID) AS Code
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%cadena_de_busqueda%'

Obtener stored procedures modificados en los ultimos n dias.

Ejemplo para 7 dias:

SELECT name,modify_date
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7
ORDER BY modify_date DESC

Como poner en cero la parte de hora de una variable o columna en SQL Server

DATEADD(dd, 0, DATEDIFF(dd, 0, **FECHA**))

Da como resultado:

SELECT GETDATE()
2009-05-29 16:07:18.373
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
2009-05-29 00:00:00.000

Probado en SQL Server 2005