October 2013 Entries

Get list of all default values for every table

This script will list all default values for every table. SELECT ao.name AS TABLE_NAME, dc.name AS CONSTRAINT_NAME, dc.definition AS DEFAULT_VALUE, ac.name AS COLUMN_NAME FROM sys.default_constraints AS dc INNER JOIN sys.all_columns AS ac ON dc.parent_object_id = ac.object_id AND dc.parent_column_id = ac.column_id INNER JOIN sys.all_objects AS ao ON ac.object_id = ao.object_id

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

List all stored procedures in a database

This script will provide a list of all stored procedures in a database along with the create script for each. SELECT specific_name, routine_definition FROM information_schema.routines WHERE routine_type = 'PROCEDURE'

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

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. 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' ...

List all Primary Keys in a database

This script will list all primary keys in a database. SELECT ST.name AS TABLE_NAME, SKC.name AS CONSTRAINT_NAME, SC.name AS CONSTRAINT_COLUMN_NAME, SC.column_id AS COLUMN_ORDER FROM sys.key_constraints AS SKC INNER JOIN sys.tables AS ST ON ST.object_id = SKC.parent_object_id INNER JOIN sys.index_columns AS SIC ON SIC.object_id = ST.object_id AND SIC.index_id = SKC.unique_index_id INNER JOIN sys.columns AS SC ON SC.object_id = ST.object_id AND SC.column_id = SIC.column_id WHERE (LEFT(ST.name, 1) = N't') AND (ST.type = N'U')

Get list of columns in every table

Here is a script to get a list of columns for each table. SELECT t.name AS TABLE_NAME, c.name AS COLUMN_NAME, c.column_id AS COLUMN_ORDER, c.system_type_id AS DATA_TYPE, c.max_length, c.precision, c.scale, c.is_nullable, c.is_identity FROM sys.columns AS c INNER JOIN sys.tables AS t ON c.object_id = t.object_id

List all Foreign Keys in a database

Here is a script to list all foreign keys in a database. SELECT o1.name AS TABLE_NAME, s.name AS CONSTRAINT_NAME, c1.name AS FOREIGN_KEY, o2.name AS FOREIGN_TABLE, c2.name AS FT_PRIMARY_KEY FROM sys.sysforeignkeys AS fk INNER JOIN sys.sysobjects AS o1 ON fk.fkeyid = o1.id INNER JOIN sys.sysobjects AS o2 ON fk.rkeyid = o2.id INNER JOIN sys.syscolumns AS c1 ON c1.id = o1.id AND c1.colid = fk.fkey INNER JOIN sys.syscolumns AS c2 ON c2.id = o2.id AND c2.colid = fk.rkey INNER JOIN sys.sysobjects AS s ON fk.constid = s.id

List of all views and scripts to create

Here is a script to list all views in a database and provide the script to create them.  This helps when moving views to new environments. SELECT a.name AS VIEW_NAME, LEFT(b.definition, 4000) AS SQL1, SUBSTRING(b.definition, 4001, 4000) AS SQL2, LEN(b.definition) AS SQL_Length FROM sys.sql_modules AS b INNER JOIN sys.views AS a ON b.object_id = a.object_id

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...