List all indexes in a database

This script will list all indexes and their types (clustered/nonclustered).

SELECT  so.name AS TableName
            , si.name AS IndexName
            , si.type_desc AS IndexType
FROM            sys.indexes si 
JOIN sys.objects so ON si.[object_id] = so.[object_id]
WHERE so.type = 'U'    --Only get indexes for User Created Tables
AND si.name IS NOT NULL
ORDER BY so.name, si.type 
[tweetmeme only_single=”false”]

Summary of last night’s job run

Here is a script that will provide a summary of jobs that ran last night.
This is helpful if you want a summary of your jobs instead of an email from each job.

(You can setup one job to just email you the results of this script).

SELECT
    j.name, 
    last_run_outcome = 
    CASE 
        WHEN js.last_run_outcome = 0 THEN 'Failed' 
        WHEN js.last_run_outcome = 1 THEN 'Succeeded' 
        WHEN js.last_run_outcome = 2 THEN 'Retry' 
        WHEN js.last_run_outcome = 3 THEN 'Canceled' 
        ELSE 'Unknown' 
    END, 
    last_run_datetime = msdb.dbo.agent_datetime( 
        CASE WHEN js.last_run_date = 0 THEN NULL ELSE js.last_run_date END, 
        CASE WHEN js.last_run_time = 0 THEN NULL ELSE js.last_run_time END) 
FROM msdb.dbo.sysjobs j 
JOIN msdb.dbo.sysjobsteps js 
ON j.job_id = js.job_id
WHERE msdb.dbo.agent_datetime(CASE WHEN js.last_run_date = 0 THEN NULL ELSE js.last_run_date END, 
                            CASE WHEN js.last_run_time = 0 THEN NULL ELSE js.last_run_time END) > DATEADD (dd , -1 , GETDATE())
ORDER BY Name
[tweetmeme only_single=”false”]

List all synonyms in a database

This script will list all synonyms you have in a database.  It will tell you the name, the server connecting to, the database, schema, and object name your connecting to.

This is helpful to figure out if you have your synonyms pointing to the wrong environment.

SELECT name, 
COALESCE(PARSENAME(base_object_name,4),@@servername) AS serverName, 
COALESCE(PARSENAME(base_object_name,3),DB_NAME(DB_ID())) AS dbName, 
COALESCE(PARSENAME(base_object_name,2),SCHEMA_NAME(SCHEMA_ID())) AS schemaName, 
PARSENAME(base_object_name,1) AS objectName 
FROM sys.synonyms 
ORDER BY serverName,dbName,schemaName,objectName
[tweetmeme only_single=”false”]

List all tables in a database

If you ever have the need to list all tables in your database, here is a statement that you can use.
I find it helpful if you need to loop through all the tables in your database or sometimes I need to provide a list to someone else.  I have used this script when moving changes between environments as a quick check to make I didn’t forget table.s

There are many different reasons you may need a list tables.

This will return just the table name.

SELECT name AS TABLE_NAME 
FROM sys.tables ORDER BY name
[tweetmeme only_single=”false”]

Query to get database information

Prashanth Jayaram provided a blog post about a script he has to get information about databases on your server.
This is a perfect script for keeping up with what’s going on in your environment or getting an idea about what’s going on a client’s site.

The script Prashanth provided had some errors, so I corrected those you can just copy the code below, paste it in SSMS and run it.

This is the information returned:
Servername
Database Name
[Status]
DataFiles
Data MB
LogFiles
Log MB
TotalSizeMB
Updateability
UserAccess
RecoveryModel
Version
Compatibility Level
Creation date
Last Backup Date

SELECT @@SERVERNAME Servername,
CONVERT(VARCHAR(25), DB.name) AS dbName,
CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],
(SELECT COUNT(1) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid !=0 ) AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0) AS [Data MB],
(SELECT COUNT(1) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS [Log MB],
(SELECT SUM((size*8)/1024) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0)+(SELECT SUM((size*8)/1024) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) TotalSizeMB,
convert(sysname,DatabasePropertyEx(name,'Updateability'))  Updateability,
convert(sysname,DatabasePropertyEx(name,'UserAccess')) UserAccess ,
convert(sysname,DatabasePropertyEx(name,'Recovery')) RecoveryModel ,
convert(sysname,DatabasePropertyEx(name,'Version')) Version ,
CASE COMPATIBILITY_LEVEL 
WHEN 60 THEN '60 (SQL Server 6.0)'
WHEN 65 THEN '65 (SQL Server 6.5)'
WHEN 70 THEN '70 (SQL Server 7.0)'
WHEN 80 THEN '80 (SQL Server 2000)'
WHEN 90 THEN '90 (SQL Server 2005)'
WHEN 100 THEN '100 (SQL Server 2008)'
END AS [compatibility level],
CONVERT(VARCHAR(20),  create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],
ISNULL((SELECT TOP 1
CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +
CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +
CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +
' (' + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ' '+ 'seconds)'
FROM msdb.dbo.backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup]
FROM sys.databases DB
ORDER BY dbName, [Last backup] DESC, NAME
[tweetmeme only_single=”false”]

Record count of all tables in a database

Here is a sql script that will give a count of all records in every table in
your database.

This comes from my friend David McCollough.

CREATE TABLE #sizes ([name] nvarchar(200), 
             [rowcount] varchar(25))
DECLARE @tablename VARCHAR (128)

DECLARE tables CURSOR FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'
   order by TABLE_NAME

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
   FROM tables
   INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
  --faster than select count(*)
  exec ('Insert into #sizes Select ''' +  @tablename + ''' as [name],(SELECT rows FROM sysindexes WHERE id = OBJECT_ID(''' +  @tablename + ''') AND indid < 2)')
FETCH NEXT
   FROM tables
   INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

select * from #sizes Order by [name]
drop table #sizes

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }