Month: October 2012

Whilst taking over from the outgoing DBA I wrote this handy query to list the SQL-Jobs he owned …

--list_job_owners.sql
SELECT name job_name, SUSER_SNAME(owner_sid) job_owner
FROM msdb.dbo.SysJobs
ORDER BY name;

… and here’s the code to change them to ‘sa’ …

--change_job_owners.sql
EXEC msdb.dbo.sp_manage_jobs_by_login
    @action = N'REASSIGN',
    @current_owner_login_name = N'SomeLoginName',
    @new_owner_login_name = N'sa';

Here’s a handy script to list the sizes of all tables in the current database …

-- biggest_tables.sql

CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))

SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''

SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
order by a.row_count desc --row_count
--order by cast(COUNT(*) as int) desc --col_count
--ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC --data_size

DROP TABLE #temp