re-size tempdb file counts and sizes

USE master
GO
DECLARE @BASEPATH VARCHAR(200)
DECLARE @PATH VARCHAR(200)
DECLARE @SQL_SCRIPT VARCHAR(500)
DECLARE @NUM_OF_FILES INT
DECLARE @FILECOUNT INT
DECLARE @SIZE INT
DECLARE @GROWTH INT
DECLARE @ISPERCENT INT

SELECT @NUM_OF_FILES = 12 -- Number of tempdb files you want to have.
SELECT @SIZE = 10240      -- Size in MB
SELECT @GROWTH = 10      
SELECT @ISPERCENT = 1

PRINT @NUM_OF_FILES

SET @BASEPATH = (select SUBSTRING(physical_name, 1, CHARINDEX(N'tempdb.mdf', LOWER(physical_name)) - 1) DataFileLocation
FROM master.sys.master_files
WHERE database_id = 2 and FILE_ID = 1)
PRINT @BASEPATH

SET @FILECOUNT = (SELECT COUNT(*)
FROM master.sys.master_files
WHERE database_id = 2 AND TYPE_DESC = 'ROWS')

WHILE @FILECOUNT > @NUM_OF_FILES
BEGIN
    SET @SQL_SCRIPT = 'ALTER DATABASE tempdb
    REMOVE FILE tempdev' + RTRIM(CAST(@FILECOUNT as CHAR))
    EXEC(@SQL_SCRIPT)
    PRINT 'Removed ' + @BASEPATH + 'tempdev' + RTRIM(CAST(@FILECOUNT as CHAR)) + '.ndf'
    SET @FILECOUNT = @FILECOUNT - 1
END

WHILE @NUM_OF_FILES > @FILECOUNT
BEGIN
    SET @SQL_SCRIPT = 'ALTER DATABASE tempdb
    ADD FILE
    (
    FILENAME = ''' + @BASEPATH + 'tempdb' + RTRIM(CAST(@NUM_OF_FILES as CHAR)) + '.ndf'',
    NAME = tempdev' + RTRIM(CAST(@NUM_OF_FILES as CHAR)) + ',
    SIZE = ' + RTRIM(CAST(@SIZE as CHAR)) + 'MB,
    FILEGROWTH = ' + RTRIM(CAST(@GROWTH as CHAR))
    IF @ISPERCENT > 0
    SET @SQL_SCRIPT = @SQL_SCRIPT + '%'
    SET @SQL_SCRIPT = @SQL_SCRIPT + ')'

    EXEC(@SQL_SCRIPT)
    PRINT 'Created ' + @BASEPATH + 'tempdev' + RTRIM(CAST(@NUM_OF_FILES as CHAR)) + '.ndf'
    SET @NUM_OF_FILES = @NUM_OF_FILES - 1
END

WHILE @FILECOUNT > 0
BEGIN
    SET @SQL_SCRIPT = 'ALTER DATABASE tempdb
    MODIFY FILE ( NAME = tempdev' + RTRIM(CASE WHEN CAST(@FILECOUNT as CHAR) = '1' THEN '' ELSE CAST(@FILECOUNT as CHAR) END) + ',
    SIZE = ' + RTRIM(CAST(@SIZE as CHAR)) + 'MB ,
    FILEGROWTH = ' + RTRIM(CAST(@GROWTH as CHAR))
    IF @ISPERCENT > 0
    SET @SQL_SCRIPT = @SQL_SCRIPT + '%'
    SET @SQL_SCRIPT = @SQL_SCRIPT + ')'
    EXEC(@SQL_SCRIPT)
    PRINT 'Modified ' + @BASEPATH + 'tempdev' + RTRIM(CASE WHEN CAST(@FILECOUNT as CHAR) = '1' THEN '' ELSE CAST(@FILECOUNT as CHAR) END) + '.ndf'
    SET @FILECOUNT = @FILECOUNT - 1
END
GO

isuniqueidentifier

CREATE FUNCTION dbo.isuniqueidentifier (@ui varchar(50))  
RETURNS bit AS  
BEGIN

RETURN case when
    substring(@ui,9,1)='-' and
    substring(@ui,14,1)='-' and
    substring(@ui,19,1)='-' and
    substring(@ui,24,1)='-' and
    len(@ui) = 36 then 1 else 0 end

END
GO

SQL DB mail

EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Default',
        @from_address = 'palexander@csod.com',
        @recipients= 'palexander@csod.com',
        @subject = 'test',
        @Body_Format = 'HTML',
        @body = 'test reply to me if you get this email'



use msdb
go
SELECT * FROM sysmail_allitems order by 1 desc
SELECT * FROM sysmail_faileditems order by 1 desc
SELECT * FROM sysmail_sentitems order by 1 desc


EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' --- check members of the DatabaseMailUserRole

EXEC msdb.dbo.sysmail_help_principalprofile_sp

EXECUTE dbo.sysmail_help_status_sp ; --- check status of Database Mail

EXECUTE dbo.sysmail_start_sp ---start Database Mail in a mail host database
EXECUTE dbo.sysmail_stop_sp ---start Database Mail in a mail host database

EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail'

EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp
    @principal_name = 'guest',
    @profile_name = 'Default' ;
   

EXECUTE msdb.dbo.sysmail_delete_profile_sp
    @profile_name = 'Default' ;

Tracking database recovery progress using information from DMV

This script is a modified script from the this article :


USE dbacollection
GO

DROP TABLE [dbo].[tbl_recovery_tracking]
GO

DROP TABLE [dbo].[tbl_dm_tran_database_transactions]
GO

CREATE TABLE [dbo].[tbl_recovery_tracking](
      [runtime] [datetime] NOT NULL,
      [command] [nvarchar](256) NOT NULL,
      [session_id] [smallint] NOT NULL,
      [database_id] [smallint] NOT NULL,
      [total_elapsed_time] [int] NOT NULL,
      [percent_complete] [real] NOT NULL,
      [estimated_completion_time] [bigint] NOT NULL,
      [wait_resource] [nvarchar](256) NOT NULL,
      [wait_time] [int] NOT NULL,
      [wait_type] [nvarchar](60) NULL,
      [blocking_session_id] [smallint] NULL,
      [reads] [bigint] NOT NULL,
      [writes] [bigint] NOT NULL,
      [cpu_time] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_dm_tran_database_transactions](
      [runtime] [datetime] NOT NULL,
      [transaction_id] [bigint] NOT NULL,
      [database_id] [int] NOT NULL,
      [database_transaction_log_record_count] [bigint] NOT NULL,
      [database_transaction_log_bytes_used] [bigint] NOT NULL,
      [database_transaction_log_bytes_reserved] [bigint] NOT NULL,
      [database_transaction_next_undo_lsn] [numeric](25, 0) NULL
) ON [PRIMARY]
GO

-- collect the information in a loop
declare @exittime datetime = dateadd(second, 60, getdate())

WHILE 1 = 1
BEGIN
      INSERT INTO [dbo].[tbl_recovery_tracking]
      SELECT GETDATE() as runtime, command,
      session_id, database_id, total_elapsed_time,
      percent_complete, estimated_completion_time,
      wait_resource, wait_time, wait_type, blocking_session_id,
      reads, writes, cpu_time
      FROM sys.dm_exec_requests
      WHERE command = 'DB STARTUP' -- may need to change this if troubleshooting recovery as part of attach database or restore

      INSERT INTO tbl_dm_tran_database_transactions
      SELECT GETDATE() as runtime,
      transaction_id, database_id,
      database_transaction_log_record_count, database_transaction_log_bytes_used,
      database_transaction_log_bytes_reserved, database_transaction_next_undo_lsn
      FROM sys.dm_tran_database_transactions
    
      WAITFOR DELAY '00:00:01'            -- change this capture interval
      if @exittime < getdate()
        break
       
END
GO
-- after you collect information for some time, you can analyze the information to understand the progress of recovery
SELECT runtime,   command,
      session_id, database_id, total_elapsed_time,
      percent_complete, estimated_completion_time,
      wait_resource, wait_time, wait_type, blocking_session_id,
      reads, writes, cpu_time
FROM [dbo].[tbl_recovery_tracking]
WHERE session_id = 50         -- change this
ORDER BY runtime
GO

SELECT
runtime, transaction_id, database_id,
database_transaction_log_record_count,
database_transaction_log_bytes_used, database_transaction_log_bytes_reserved,
database_transaction_next_undo_lsn
FROM tbl_dm_tran_database_transactions
WHERE database_id = 49 and transaction_id = 428607070         -- change this
ORDER BY runtime

GO

parameterized output valus with dynamic query

declare @sql nvarchar(4000), @tablename nvarchar(100), @reccount int , @param_definition nvarchar(4000) , @param_value int
set @tablename  = 'LA4-PRD-WWW103_W3SVC2_ex120917'

set @sql = 'select @reccount_out = COUNT(*) from ['+@tablename+']'
set @param_definition = N'@reccount_out int OUTPUT';
print @sql

exec sp_executesql @sql , @param_definition, @reccount_out = @reccount output;
print @reccount

Change Schema owner of objects in DB

use ces_AUTOMATION_LD4PILOT
go

declare @name nvarchar(1000), @sql nvarchar(4000)
------------------------
--rename schem onwer for users
declare tmp_cur2 cursor for
SELECT 'ALTER SCHEMA [DBO] TRANSFER [' + s.Name + '].' + o.Name
    FROM sys.Objects o
    INNER JOIN sys.Schemas s on o.schema_id = s.schema_id
    WHERE s.name = 'ToBeDeleted'
    And (o.Type = 'U' Or o.Type = 'P' Or o.Type = 'V')
   
open tmp_cur2
Fetch next from tmp_cur2 into @sql

while @@fetch_status = 0
begin
print @sql
exec sp_executesql @sql
Fetch next from tmp_cur2 into @sql
print '---'
end

close tmp_cur2
deallocate tmp_cur2          

drop orphan users and rename current objects schema

CREATE SCHEMA [ToBeDeleted]
go


declare @name nvarchar(1000), @sql nvarchar(4000)
declare tmp_cur cursor for
SELECT     u.name --u.*--, l.*
FROM    sysusers U
left outer join master..syslogins L on U.sid = L.sid
where l.sid is null and u.altuid is null and u.status <> 4
and u.name not in ('sys','guest','INFORMATION_SCHEMA')
order by u.name


open tmp_cur
Fetch next from tmp_cur into @name

while @@fetch_status = 0
begin
    print @name
    set @sql = ''

    ------------------------
    --rename schem onwer for users
    declare tmp_cur2 cursor for
    SELECT 'ALTER SCHEMA [ToBeDeleted] TRANSFER [' + s.Name + '].' + o.Name
        FROM sys.Objects o
        INNER JOIN sys.Schemas s on o.schema_id = s.schema_id
        WHERE s.Name = @name
        And (o.Type = 'U' Or o.Type = 'P' Or o.Type = 'V')
       
    open tmp_cur2
    Fetch next from tmp_cur2 into @sql

    while @@fetch_status = 0
    begin
    print @sql
    exec sp_executesql @sql
    Fetch next from tmp_cur2 into @sql
    print '---'
    end

    close tmp_cur2
    deallocate tmp_cur2   


    ------------------------
    --drop users and schema

    set @sql = 'DROP SCHEMA ['+@name+']'
    print @sql
    exec sp_executesql @sql

    set @sql = 'DROP USER ['+@name+']'
    print @sql
    exec sp_executesql @sql
    ------------------------

    Fetch next from tmp_cur into @name
    print '---'
end

close tmp_cur
deallocate tmp_cur

-------------------------------------------------------------------------

List of inactive users in a DB

SELECT     u.*, l.*
FROM    sysusers U
left outer join master..syslogins L on U.sid = L.sid
where l.sid is null and u.altuid is null and u.status <> 4
and u.name not in ('sys','guest','INFORMATION_SCHEMA')
order by u.name

In SSIS how to continue a for each loop container even if its inner tasks fails (Usage of Propogate Property in SSIS)

http://www.codeproject.com/Articles/384690/In-SSIS-how-to-continue-a-for-each-loop-container

Recompile Stuck Procs : sp_recompile_stuck_procs

USE [master]
GO
/*
exec sp_recompile_stuck_procs 2
*/
CREATE PROCEDURE [dbo].[sp_recompile_stuck_procs]
(
@maxcount int = 10
)
AS
BEGIN
    SET NOCOUNT ON;


declare @datestr nvarchar(100), @sql nvarchar(4000), @tblname nvarchar(100)
--declare @maxcount int = 10
select @datestr = CONVERT(nvarchar(10),getdate(), 112)+'_'+CONVERT(nvarchar(10),datepart(hour,getdate()))+'_'+CONVERT(nvarchar(10),datepart(minute,getdate()))+'_'+CONVERT(nvarchar(10),datepart(SECOND,getdate()))
--select @datestr
set @tblname = '__drop_who3_collection_1_minute_'+@datestr

set @sql = 'IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+@tblname+']'') AND type in (N''U''))
create table '+@tblname+'
(SPID bigint,Status varchar(100),Login varchar(100),Host varchar(100),BlkBy bigint, TempDBWait varchar(100),DBName varchar(100),CommandType varchar(100),
SQLStatement varchar(max),ObjectName varchar(100),ElapsedMS bigint,CPUTime bigint,IOReads bigint,IOWrites bigint,LastWaitType varchar(100),
StartTime datetime,Protocol varchar(100),ConnectionWrites bigint,ConnectionReads bigint,ClientAddress  varchar(100),Authentication  varchar(100), Collectiontime datetime)
'
--print @sql
exec sp_executesql @sql   

set @sql = 'insert into '+@tblname+'
exec sp_who3 '
--print @sql
exec sp_executesql @sql   

set @sql = 'select dbname, objectname, count(*) as cc into '+@tblname+'_loop from '+@tblname+' where objectname is not null group by  dbname, objectname having count(*) > '+CONVERT(varchar, @maxcount)
--print @sql
exec sp_executesql @sql   

--set @sql = 'select * from '+@tblname
----print @sql
--exec sp_executesql @sql   

--set @sql = 'select * from '+@tblname+'_loop'
----print @sql
--exec sp_executesql @sql   


---------------------------------------------------------
--declare @tblname nvarchar(100) = '__drop_who3_collection_1_minute_20120917_13_37_16' , @sql nvarchar(1000)
declare @tbl table (db nvarchar(100) , sp nvarchar(100), cc int)
declare @db nvarchar(100) , @sp nvarchar(100)

set @sql = 'select dbname, objectname, cc From '+@tblname+'_loop'

insert into @tbl
exec sp_executesql @sql

declare tmp_cur cursor for
select db, sp From @tbl

open tmp_cur
Fetch next from tmp_cur into @db, @sp

while @@fetch_status = 0
begin
    --print  @db
    --print @sp
    set @sql = 'use ' +@db +'
    exec sp_recompile '+replace(@sp, 'dbo.','')

    print @sql
    --exec sp_executesql @sql


Fetch next from tmp_cur into  @db, @sp
print '---'
end

close tmp_cur
deallocate tmp_cur

-----------------------------------
if (select COUNT(*) from @tbl) > 0
begin

DECLARE @tableHTML  NVARCHAR(MAX) ='...' , @subjectstring nvarchar(200)='...'

SET @tableHTML =
    N'

Proc Re-compile on '+@@SERVERNAME+'

' +
    N'' +
    N' ' +
    N' ' +
    CAST ( ( SELECT td = db,       '',
                    td = sp,       '',
                    td = cc
              FROM @tbl
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'

Database	Proc	Counts

' ;
   
    print @tableHTML
   
set @subjectstring = 'Alert : Proc Re-compile on SQL Server '+@@SERVERNAME
print @subjectstring

        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'default',
            @recipients ='patrick@csod.com',
            @body = @tableHTML,
            @body_format = 'HTML' ,
            @subject = @subjectstring;
           
end

---------------------------------------

set @sql = 'drop table '+@tblname+'_loop'
--print @sql
exec sp_executesql @sql   



set @sql = 'drop table '+@tblname
--print @sql
exec sp_executesql @sql   


END

determine last time file growth happened

http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/662f4e65-0091-49e6-a497-ec23a8bb1ca2/

Reindex Online tables - maintenance_re_index

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*
maintenance_re_index
*/
ALTER Procedure maintenance_re_index
(
    @MaxFragmentation int = 10 ,
    @TrivialPageCount int = 100 ,
    @RebuildThreshold int = 40
)
as
BEGIN

SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @HasBlobColumn int;

--DECLARE @MaxFragmentation int;
--DECLARE @TrivialPageCount int;
--DECLARE @RebuildThreshold int;

---- Tuning constants
--SET @MaxFragmentation = 10 --Change this value to adjust the threshold for fragmentation
--SET @RebuildThreshold = 30 --Change this value to adjust the break point for defrag/rebuild
--SET @TrivialPageCount = 1000 --Change this value to adjust the size threshold

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
if(OBJECT_ID('tempdb..#work_to_do')>1)
    DROP TABLE #work_to_do;

SELECT distinct
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > @MaxFragmentation
AND index_id > 0 -- cannot defrag a heap
AND page_count > @TrivialPageCount -- ignore trivial sized indexes

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
SELECT distinct WTD.*
FROM #work_to_do WTD
INNER JOIN sys.indexes I ON I.object_id = WTD.objectid
WHERE I.is_disabled = 0 AND I.is_hypothetical = 0;

-- Open the cursor.

OPEN partitions;

-- Loop through the partitions.

WHILE (1=1)
    BEGIN;
        FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
            SET @HasBlobColumn = 0 -- reinitialize
            SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
            FROM sys.objects AS o
            JOIN sys.schemas AS s ON s.schema_id = o.schema_id  WHERE o.object_id = @objectid;
           
            SELECT @indexname = QUOTENAME(name)
            FROM sys.indexes
            WHERE object_id = @objectid AND index_id = @indexid;

            SELECT @partitioncount = count (*)
            FROM sys.partitions
            WHERE object_id = @objectid AND index_id = @indexid;

            -- Check for BLOB columns
            IF @indexid = 1 -- only check here for clustered indexes ANY blob column on the table counts
                SELECT @HasBlobColumn = CASE WHEN max(so.object_ID) IS NULL THEN 0 ELSE 1 END
                FROM sys.objects SO
                inner join sys.columns SC ON SO.Object_id = SC.object_id
                inner join sys.types ST ON SC.system_type_id = ST.system_type_id AND
                (
                    ST.name IN ('varchar', 'nvarchar', 'varbinary') AND SC.max_length = -1
                    OR
                    ST.name IN ('text', 'ntext', 'image', 'xml')
                )
                WHERE SO.Object_ID = @objectID
            ELSE -- nonclustered. Only need to check if indexed column is a BLOB
                SELECT @HasBlobColumn = CASE WHEN max(so.object_ID) IS NULL THEN 0 ELSE 1 END
                FROM sys.objects SO
                INNER JOIN sys.index_columns SIC ON SO.Object_ID = SIC.object_id
                INNER JOIN sys.Indexes SI ON SO.Object_ID = SI.Object_ID AND SIC.index_id = SI.index_id
                INNER JOIN sys.columns SC ON SO.Object_id = SC.object_id AND SIC.Column_id = SC.column_id
                INNER JOIN sys.types ST ON SC.system_type_id = ST.system_type_id AND
                (
                    ST.name IN ('varchar', 'nvarchar', 'varbinary') AND SC.max_length = -1
                    OR
                    ST.name IN ('text', 'ntext', 'image', 'xml')
                )
                WHERE SO.Object_ID = @objectID
           
                SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname;
                IF @frag > @RebuildThreshold
                BEGIN
                    SET @command = @command + N' REBUILD'
                    IF @HasBlobColumn = 1
                        SET @command = @command + N' WITH( SORT_IN_TEMPDB = ON) '
                    ELSE
                        SET @command = @command + N' WITH( ONLINE = ON, SORT_IN_TEMPDB = ON) '
                END
                ELSE
                    SET @command = @command + N' REORGANIZE'
                    IF @partitioncount > 1
                        SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
                        PRINT N'Executing: ' + @command +' Has Blob = ' + convert(nvarchar(2),@HasBlobColumn);
                       
                        --PRINT  @command
                        --print 'print '''+@command +''''
                        --print 'Go'
                        EXEC (@command)
        END;
        -- Close and deallocate the cursor.
        CLOSE partitions;
        DEALLOCATE partitions;
END

parent child relation hierachy script

with cte as
(
select 1 as lvl, SpaceShuttleID , ParentSpaceShuttleID from SpaceShuttle where SpaceShuttleID = 200
union all
select cte.lvl + 1, b.SpaceShuttleID , b.ParentSpaceShuttleID  from SpaceShuttle b join cte on cte.SpaceShuttleID = b.ParentSpaceShuttleID
)
select * from cte order by lvl;

modify sysjobs trigger to send alert

USE [msdb]
GO
/****** Object:  Trigger [dbo].[trig_sysjobs_insert_update]    Script Date: 08/17/2012 08:56:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER TRIGGER [dbo].[trig_sysjobs_insert_update]
ON [dbo].[sysjobs]
FOR INSERT, UPDATE
AS
BEGIN
  SET NOCOUNT ON
  -- Disallow the insert or update if the originating_server_id isn't in sysoriginatingservers_view. 
  IF (EXISTS (SELECT *
            FROM inserted
           WHERE inserted.originating_server_id NOT IN
                    (SELECT v.originating_server_id
                     FROM sysoriginatingservers_view AS v)))
  BEGIN
   RAISERROR(14379, -1, -1, 'dbo.sysjobs')
   ROLLBACK TRANSACTION
    RETURN
  END
  else
  begin

    Declare @Body1 nvarchar(max)
    Select @Body1 = 'SQL Server Job Name: ' + name + ' modified on '+convert(varchar, date_modified,9) from inserted

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'default',
    @recipients = 'palexander@csod.com',
    @body = @Body1,
    @subject = 'SQL Server Job Modification Alert'

  end
END

SQL job queries

--JOB INFO
select *
from sysjobs a
inner join sysjobschedules b on a.job_id = b.job_id
inner join sysschedules c on b.schedule_id = c.schedule_id
--where a.name like '%SoapFeedJobs%'
where a.name like '%CES Daily Maintenance - VLINE%'


--JOB HISTORY
select *
from msdb.dbo.sysjobs j
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id AND h.step_id = 0 --AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time
--where j.name like '%SoapFeedJobs-ces_Akamai%'
where j.name like '%CES Daily Maintenance - VLINE%'

 --JOB HISTORY with outcome result
select
Case isnull(h.run_status,'') WHEN '' THEN 'Unknown' WHEN 0 THEN 'Fail'WHEN 1 THEN 'Success' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancel' WHEN 4 THEN 'In progress' End AS LastRunOutcome,
CASE WHEN h.run_duration > 0 THEN (h.run_duration / 1000000) * (3600 * 24) + (h.run_duration / 10000 % 100) * 3600 + (h.run_duration / 100 % 100) * 60 + (h.run_duration % 100) Else NULL End AS LastRunDuration ,
*
from msdb.dbo.sysjobs j
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id AND h.step_id = 0 --AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time
--where j.name like '%SoapFeedJobs-ces_Akamai%'
where j.name like '%CES Daily Maintenance - VLINE%'

lists all the default constraints and the default values for the user tables in the database

SELECT  
        b.name AS TABLE_NAME,
        d.name AS COLUMN_NAME,
        a.name AS CONSTRAINT_NAME,
        c.text AS DEFAULT_VALUEFROM sys.sysobjects a INNER JOIN
        (SELECT name, id
         FROM sys.sysobjects 
         WHERE xtype = 'U') b on (a.parent_obj = b.id)
                      INNER JOIN sys.syscomments c ON (a.id = c.id)
                      INNER JOIN sys.syscolumns d ON (d.cdefault = a.id)                                          
 WHERE a.xtype = 'D'        
 ORDER BY b.name, a.name

function to get time for next job

use msdb
go

/*
select dbo.fn_next_job_time(15, null, null)
select dbo.fn_next_job_time(7, null, null)
select dbo.fn_next_job_time(7, 'CES Weekly Maintenance -','Weekend Sat')
select dbo.fn_next_job_time(7, 'CES Weekly Maintenance -',null)
select dbo.fn_next_job_time(4, 'CES Weekly Maintenance -',null)
*/
CREATE FUNCTION fn_next_job_time
(
    @interval int = 10,
    @jobname nvarchar(100) = null ,
    @schedulename nvarchar(100) = null
)
RETURNS int
AS
BEGIN

    set @jobname = @jobname + '%'
    set @schedulename = @schedulename + '%'

    declare @curr_hr int, @curr_min int, @curr_sec int, @next_hr int, @next_min int, @next_sec int
    declare @nexttime int
    --set @nexttime = 10004
    --set @nexttime = 206
    --set @nexttime = 5506
    --set @nexttime = 230847
    --set @nexttime = 145712
    --set @nexttime = 41942
    --set @nexttime = 225847
    --set @nexttime = 235847


    select top 1 @nexttime = c.active_start_time
    --a.schedule_id, b.name, c.active_start_time,c.name, *
    from sysjobschedules a
    inner join sysjobs b on a.job_id = b.job_id
    inner join sysschedules c on a.schedule_id = c.schedule_id
    where
    (b.name like @jobname or @jobname is null)
    and
    ( c.name like @schedulename or @schedulename is null)
    order by c.active_start_time desc
   
    select @curr_hr = right(convert(nvarchar(10), @nexttime/10000),2) , @curr_min = right(convert(nvarchar(10),  @nexttime/100),2),  @curr_sec = right(convert(nvarchar(10), @nexttime),2)
    set @next_sec = @curr_sec
    set @next_min = (@curr_min + @interval)%60
    set @next_hr =  @curr_hr + (@curr_min + @interval)/ 60
    set @next_hr = case when @next_hr >= 24 then 0 else @next_hr  end
    --select @nexttime, @curr_hr , @curr_min , @curr_sec
    --select @nexttime, @next_hr , @next_min , @next_sec
   
    select @nexttime = convert(int,
    (case when @next_hr  < 10 then '0'+CONVERT(nvarchar(10),@next_hr ) else convert(nvarchar(10),@next_hr ) end) +
    (case when @next_min  < 10 then '0'+CONVERT(nvarchar(10),@next_min ) else convert(nvarchar(10),@next_min ) end) +
    (case when @next_sec < 10 then '0'+CONVERT(nvarchar(10),@next_sec) else convert(nvarchar(10),@next_sec) end)
    )


   
    RETURN @nexttime

END
GO

list spaces used by all DBs in a server : sp_dbspaceall

use master
go
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_dbspaceall]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_dbspaceall]
GO
/*
exec sp_dbspaceall
*/
create procedure sp_dbspaceall
as
begin

Declare @FileSpace table (
[database_id] int,
[db_name] nvarchar(100),
[file_id] int,
[file_name] nvarchar(100),
[space_used] bigint
)
INSERT INTO @FileSpace  EXEC sp_MSforeachdb 'USE [?] SELECT db_id(''?''),''?'' as DBname,fileid, name ,FILEPROPERTY(name, ''SpaceUsed'')  as spaceused from sys.sysfiles';

With SpaceInfo (DBName, FileName, FileType,FilePath,CurrentSizeMB,SpaceUsedMB)
as
(

SELECT
DB_NAME(df.[database_id]) as [DBName],
df.[name] as [FileName] ,
df.type_desc as [FileType],
df.physical_name as [FilePath],
(df.[size]*8.0)/1024.0 as [CurrentSizeMB],
(fs.[space_used]*8.0)/1024.0 as [SpaceUsedMB]
FROM 
SYS.MASTER_FILES AS df
INNER JOIN @FileSpace as fs on (df.[database_id]= FS.[database_id] and df.[file_id]=fs.[file_id])
)
SELECT
SI.DBName,
convert(dec(17,2),SUM(SI.CurrentSizeMB)) as [DatabaseSizeMB],
convert(dec(14,2),(1-(SUM(SI.SpaceUsedMB)/SUM(SI.CurrentSizeMB)))*100 )as [%age Free],
convert(dec(17,2),D.DataFileSizeMB) as  DataFileSizeMB,
convert(dec(14,2) ,(1-(D.DataFileSpaceUsedMB/D.DataFileSizeMB))*100) as [%age Free],
convert(dec(17,2),L.LogFileSizeMB) as LogFileSizeMB,
convert(dec(14,2),(1-(L.LogFileSpaceUsedMB/L.LogFileSizeMB))*100)  as [%age Free]
FROM
SpaceInfo SI
CROSS APPLY (SELECT  sum(CurrentSizeMB) as [DataFileSizeMB], SUM(SpaceUsedMB) as [DataFileSpaceUsedMB] FROM SpaceInfo
WHERE filetype='ROWS' and DBName=SI.DBName group by DBName)  as D
CROSS APPLY (SELECT  sum(CurrentSizeMB) as [LogFileSizeMB], SUM(SpaceUsedMB) as [LogFileSpaceUsedMB] FROM SpaceInfo
WHERE filetype ='LOG' and DBName=SI.DBName group by DBName)  as L
GROUP BY
SI.DBName, D.DataFileSizeMB,D.DataFileSpaceUsedMB,L.LogFileSizeMB,L.LogFileSpaceUsedMB
Order by D.DataFileSizeMB desc
end

SQL Query Stress Tool

http://www.mssqltips.com/sqlservertip/2730/sql-query-stress-tool/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012717

Top SQL Server Tools SQL compliance manager SQL diagnostic manager SQL Monitor SQL Sentry Performance Advisor for SQL Server SQL safe backup SSAS Interview Questions on Measures, Actions, and Storage

http://www.mssqltips.com/sqlservertip/2637/ssas-interview-questions-part-i-questions-on-basic-concepts-data-sources-and-data-source-views/

http://www.mssqltips.com/sqlservertip/2662/sql-server-analysis-services-interview-questions-part-ii--dimensions/

http://www.mssqltips.com/sqlservertip/2683/sql-server-analysis-services-interview-questions-on-dimensions-hierarchies-and-properties/

http://www.mssqltips.com/sqlservertip/2614/ssas-interview-questions-on-measures-actions-and-storage/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012715

Importing SQL Server Data Using SSIS - Which Option is Fastest

http://www.mssqltips.com/sqlservertip/2684/importing-sql-server-data-using-ssis--which-option-is-fastest/

Preventing from Dropping database

CREATE TRIGGER [ddl_trig_Prevent_Drop_Database]
ON ALL SERVER
FOR DROP_DATABASE
AS

DECLARE @db VARCHAR(800)
SET @db = (SELECT 'Database Dropped Attempted by ' + CONVERT(nvarchar(100), ORIGINAL_LOGIN()) +
' executing command: '+ EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(229)')) + '

To temporarily allow dropping action:
DISABLE TRIGGER ddl_trig_Prevent_Drop_Database ON ALL SERVER;

!!!!!!MAKE SURE TO RE-ENABLE THE TRIGGER CHECK AS SOON AS YOU ARE DONE!!!!!!
ENABLE TRIGGER ddl_trig_Prevent_Drop_Database ON ALL SERVER;
'
RAISERROR(@db, 16, 1)WITH LOG

ROLLBACK

GO

Disable Enable indexes

select
    sys.objects.name,
    sys.indexes.name,
    'alter index '+ sys.indexes.name +' on '+sys.objects.name+' rebuild',
    'alter index '+ sys.indexes.name +' on '+sys.objects.name+' disable'
from sys.indexes
    inner join sys.objects on sys.objects.object_id = sys.indexes.object_id
where sys.indexes.is_disabled = 1
order by
    sys.objects.name,
    sys.indexes.name

Ordering and Ranking a data set

useful technique to pre-order data set


select
DENSE_RANK() over (order by name_first),
RANK() over (order by name_first),
ROW_NUMBER() over (order by name_first),
ROW_NUMBER() over (order by name_last),
*
from users
order by name_first, name_last

Database Encryption with DB Certificates

SQL Servers Database Encryption on Swim lanes

This document is create on May 2^nd, 2012 and is valid for Swim Lanes. Certificates are installed on LD4PRDCLT101 and this version of the certificate and key should be used across all swim lanes if needed.

Create a new Master Key

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password';

Go

--To Drop the masterkey

--DROP MASTER KEY

Create a new Certificate per Database that need to be encrypted

--database encryption key

CREATE CERTIFICATE DatabaseName1_Cert WITH SUBJECT = 'DatabaseName1 Certificate'

Go

CREATE CERTIFICATE DatabaseName2_Cert WITH SUBJECT = 'DatabaseName2 Certificate'

Go

CREATE CERTIFICATE DatabaseName3_Cert WITH SUBJECT = 'DatabaseName3 Certificate'

Go

--To Drop the certificate

-- DROP CERTIFICATE DatabaseName1_Cert

Backup Master Key and certificates and use for restoring databases on a different server

      Use master

GO

Backup master key to file = 'C:\MasterKey.key' encryption by password = 'Password' ;

BACKUP CERTIFICATE DatabaseName1_Cert TO FILE = 'c:\DatabaseName1_Cert_file.cert'

WITH PRIVATE KEY ( FILE = 'c:\DatabaseName1_Cert_key.cert' ,

ENCRYPTION BY PASSWORD = 'Password'

);

BACKUP CERTIFICATE DatabaseName2_Cert TO FILE = 'c:\DatabaseName2_Cert_file.cert'

WITH PRIVATE KEY ( FILE = 'c:\DatabaseName2_Cert_key.cert' ,

ENCRYPTION BY PASSWORD = 'Password'

);

BACKUP CERTIFICATE DatabaseName3_Cert TO FILE = 'c:\DatabaseName3_Cert_file.cert'

WITH PRIVATE KEY ( FILE = 'c:\DatabaseName3_Cert_key.cert' ,

ENCRYPTION BY PASSWORD = 'Password'

);

Create certificates from a backup certificate on another server, (note:  the master key should be created before this can be done)

CREATE CERTIFICATE DatabaseName1_Cert

FROM FILE = 'C:\DatabaseName1_Cert_file.cert'

WITH PRIVATE KEY (FILE = 'C:\DatabaseName1_Cert_key.cert' , DECRYPTION BY PASSWORD = 'Password')

CREATE CERTIFICATE DatabaseName2_Cert

FROM FILE = 'C:\DatabaseName2_Cert_file.cert'

WITH PRIVATE KEY (FILE = 'C:\DatabaseName2_Cert_key.cert' , DECRYPTION BY PASSWORD = 'Password')

CREATE CERTIFICATE DatabaseName3_Cert

FROM FILE = 'C:\DatabaseName3_Cert_file.cert'

WITH PRIVATE KEY (FILE = 'C:\DatabaseName3_Cert_key.cert' , DECRYPTION BY PASSWORD = 'Password')

Encrypt a Database

USE [DatabaseName1]

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE DatabaseName1_Cert

GO

ALTER DATABASE [DatabaseName1]

SET ENCRYPTION ON

GO

USE [DatabaseName2]

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE DatabaseName2_Cert

GO

ALTER DATABASE [DatabaseName2]

SET ENCRYPTION ON

GO

USE [DatabaseName3]

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE DatabaseName3_Cert

GO

ALTER DATABASE [DatabaseName3]

SET ENCRYPTION ON

GO

Restore the Databases on a separate server, the key and certificates should be already created and restored. If the certificates are not created properly the restore will fail.

RESTORE DATABASE [DatabaseName1_retored]

FROM  DISK = N'c:\DatabaseName1.bak' WITH  FILE = 1, 

MOVE N'Data' TO N'E:\DATA_Release\DatabaseName1.mdf', 

MOVE N'Log' TO N'E:\LOG_Release\DatabaseName1.LDF', 

NOUNLOAD,  REPLACE,  STATS = 10

RESTORE DATABASE [DatabaseName2_retored]

FROM  DISK = N'c:\DatabaseName2.bak' WITH  FILE = 1, 

MOVE N'Data' TO N'E:\DATA_Release\DatabaseName2.mdf', 

MOVE N'Log' TO N'E:\LOG_Release\DatabaseName2.LDF', 

NOUNLOAD,  REPLACE,  STATS = 10

RESTORE DATABASE [DatabaseName3_retored]

FROM  DISK = N'c:\DatabaseName3.bak' WITH  FILE = 1, 

MOVE N'Data' TO N'E:\DATA_Release\DatabaseName3.mdf', 

MOVE N'Log' TO N'E:\LOG_Release\DatabaseName3.LDF', 

NOUNLOAD,  REPLACE,  STATS = 10

View which Databases are encrypted

SELECT db_name(database_id), encryption_state, *

FROM sys.dm_database_encryption_keys with (nolock)

System Tables to view the encryption keys

SELECT * FROM sys.symmetric_keys

SELECT * FROM sys.certificates

SELECT * FROM sys.dm_database_encryption_keys

Remove Encryption from Database

ALTER DATABASE [DatabaseName1];

SET ENCRYPTION OFF;

GO

use [DatabaseName1]

Go

DROP DATABASE ENCRYPTION KEY;

GO

list of failed jobs

SELECT name FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0

Querying the SQL Server System Catalog FAQ

How do I find all the tables that do not have a clustered index in a specified database?

Before you run the following queries, replace  with a valid database name.

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name
FROM sys.tables AS t
WHERE NOT EXISTS 
   (
     SELECT * FROM sys.indexes AS i
     WHERE i.object_id = t.object_id
     AND i.type = 1  -- or type_desc = 'CLUSTERED'
   )
ORDER BY schema_name, table_name;
GO

Or, you can use the OBJECTPROPERTY function as shown in the following example.

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name, name AS table_name
FROM sys.tables 
WHERE OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0
ORDER BY schema_id, name;
GO

TOP

How do I find all the owners of entities contained in a specified schema?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT 'OBJECT' AS entity_type
    ,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name
    ,name 
FROM sys.objects WHERE SCHEMA_NAME(schema_id) = ''
UNION 
SELECT 'TYPE' AS entity_type
    ,USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId')) AS owner_name
    ,name 
FROM sys.types WHERE SCHEMA_NAME(schema_id) = '' 
UNION
SELECT 'XML SCHEMA COLLECTION' AS entity_type 
    ,COALESCE(USER_NAME(xsc.principal_id),USER_NAME(s.principal_id)) AS owner_name
    ,xsc.name 
FROM sys.xml_schema_collections AS xsc JOIN sys.schemas AS s
    ON s.schema_id = xsc.schema_id
WHERE s.name = '';
GO

TOP

How do I find all the tables that do not have a primary key?

Before you run the following queries, replace  with a valid database name.

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(t.schema_id) AS schema_name
    ,t.name AS table_name
FROM sys.tables t 
WHERE object_id NOT IN 
   (
    SELECT parent_object_id 
    FROM sys.key_constraints 
    WHERE type_desc = 'PRIMARY_KEY_CONSTRAINT' -- or type = 'PK'
    );
GO

Or, you can run the following query.

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    ,name AS table_name 
FROM sys.tables 
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
ORDER BY schema_name, table_name;
GO

TOP

How do I find all the tables that do not have an index?

Before you run the following query, replace  with a valid database name.

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    ,name AS table_name
FROM sys.tables 
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0
ORDER BY schema_name, table_name;
GO

TOP

How do I find all the tables that have an identity column?

Before you run the following query, replace  with a valid database name.

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    , t.name AS table_name
    , c.name AS column_name
FROM sys.tables AS t
JOIN sys.identity_columns c ON t.object_id = c.object_id
ORDER BY schema_name, table_name;
GO

Or, you can run the following query.

Note
This query does not return the name of the columns.

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    ,name AS table_name 
FROM sys.tables 
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 1
ORDER BY schema_name, table_name;
GO

TOP

How do I find the data types of the columns of a specified table?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT c.name AS column_name
    ,c.column_id
    ,SCHEMA_NAME(t.schema_id) AS type_schema
    ,t.name AS type_name
    ,t.is_user_defined
    ,t.is_assembly_type
    ,c.max_length
    ,c.precision
    ,c.scale
FROM sys.columns AS c 
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE c.object_id = OBJECT_ID('')
ORDER BY c.column_id;
GO

TOP

How do I find the dependencies on a specified function?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT OBJECT_NAME(object_id) AS referencing_object_name
    ,COALESCE(COL_NAME(object_id, column_id), '(n/a)') AS referencing_column_name
    ,*
FROM sys.sql_dependencies
WHERE referenced_major_id = OBJECT_ID('')
ORDER BY OBJECT_NAME(object_id), COL_NAME(object_id, column_id);
GO 

TOP

How do I find all the stored procedures in a database?

Before you run the following query, replace  with a valid name.

Transact-SQL

USE ;
GO
SELECT name AS procedure_name 
    ,SCHEMA_NAME(schema_id) AS schema_name
    ,type_desc
    ,create_date
    ,modify_date
FROM sys.procedures;
GO

TOP

How do I find the parameters for a specified stored procedure or function?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    ,o.name AS object_name
    ,o.type_desc
    ,p.parameter_id
    ,p.name AS parameter_name
    ,TYPE_NAME(p.user_type_id) AS parameter_type
    ,p.max_length
    ,p.precision
    ,p.scale
    ,p.is_output
FROM sys.objects AS o
INNER JOIN sys.parameters AS p ON o.object_id = p.object_id
WHERE o.object_id = OBJECT_ID('')
ORDER BY schema_name, o.object_name, p.parameter_id;
GO

TOP

How do I find all the user-defined functions in a database?

Before you run the following query, replace  with a valid database name.

Transact-SQL

USE ;
GO
SELECT name AS function_name 
  ,SCHEMA_NAME(schema_id) AS schema_name
  ,type_desc
  ,create_date
  ,modify_date
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
GO

TOP

How do I find all views in a database?

Before you run the following query, replace  with a valid database name.

Transact-SQL

USE ;
GO
SELECT name AS view_name 
  ,SCHEMA_NAME(schema_id) AS schema_name
  ,OBJECTPROPERTYEX(object_id,'IsIndexed') AS IsIndexed
  ,OBJECTPROPERTYEX(object_id,'IsIndexable') AS IsIndexable
  ,create_date
  ,modify_date
FROM sys.views;
GO

TOP

How do I find all the entities that have been modified in the last N days?

Before you run the following query, replace  and  with valid values.

Transact-SQL

USE ;
GO
SELECT name AS object_name 
  ,SCHEMA_NAME(schema_id) AS schema_name
  ,type_desc
  ,create_date
  ,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() - 
ORDER BY modify_date;
GO

TOP

How do I find the LOB data types of a specified table?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT name AS column_name 
    ,column_id 
    ,TYPE_NAME(user_type_id) AS type_name
    ,max_length
    ,CASE 
       WHEN max_length = -1 AND TYPE_NAME(user_type_id) <> 'xml'
            THEN 1
            ELSE 0
     END AS [(max)]
FROM sys.columns
WHERE object_id=OBJECT_ID('') 
    AND ( TYPE_NAME(user_type_id) IN ('xml','text', 'ntext','image')
         OR (TYPE_NAME(user_type_id) IN ('varchar','nvarchar','varbinary')
         AND max_length = -1)
        );
GO

TOP

How do I view the definition of a module?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('');
GO

Or, you can use the OBJECT_DEFINITION function as shown in the following example.

Transact-SQL

USE ;
GO
SELECT OBJECT_DEFINITION (OBJECT_ID('')) AS ObjectDefinition;
GO

TOP

How do I view the definition of a server-level trigger?

Transact-SQL

SELECT definition
FROM sys.server_sql_modules;
GO

TOP

How do I find the columns of a primary key for a specified table?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT i.name AS index_name
    ,ic.index_column_id
    ,key_ordinal
    ,c.name AS column_name
    ,TYPE_NAME(c.user_type_id)AS column_type 
    ,is_identity
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic 
    ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c 
    ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1 
    AND i.object_id = OBJECT_ID('');
GO

Or, you can use the COL_NAME function as shown in the following example.

Transact-SQL

USE ;
GO
SELECT i.name AS index_name
    ,COL_NAME(ic.object_id,ic.column_id) AS column_name
    ,ic.index_column_id
    ,key_ordinal
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic 
    ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1 
    AND i.object_id = OBJECT_ID('');
GO

TOP

How do I find the columns of a foreign key for a specified table?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT 
    f.name AS foreign_key_name
   ,OBJECT_NAME(f.parent_object_id) AS table_name
   ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name
   ,OBJECT_NAME (f.referenced_object_id) AS referenced_object
   ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name
   ,is_disabled
   ,delete_referential_action_desc
   ,update_referential_action_desc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc 
   ON f.object_id = fc.constraint_object_id 
WHERE f.parent_object_id = OBJECT_ID('');

TOP

How do I find the permissions granted or denied to a specified principal?

The following example creates a function to return the name of the entity on which the permissions are checked. The function is invoked in the queries that follow. The function must be created in every database in which you want to check permissions.

Transact-SQL

-- Create a function to return the name of the entity on which the permissions are checked.
IF OBJECT_ID (N'dbo.entity_instance_name', N'FN') IS NOT NULL
    DROP FUNCTION dbo.entity_instance_name;
GO
CREATE FUNCTION dbo.entity_instance_name(@class_desc nvarchar(60), @major_id int) 
RETURNS sysname AS
BEGIN
    DECLARE @the_entity_name sysname
    SELECT @the_entity_name = CASE
        WHEN @class_desc = 'DATABASE' THEN DB_NAME()
        WHEN @class_desc = 'SCHEMA' THEN SCHEMA_NAME(@major_id)
        WHEN @class_desc = 'OBJECT_OR_COLUMN' THEN OBJECT_NAME(@major_id)
        WHEN @class_desc = 'DATABASE_PRINCIPAL' THEN USER_NAME(@major_id)
        WHEN @class_desc = 'ASSEMBLY' THEN 
            (SELECT name FROM sys.assemblies WHERE assembly_id=@major_id)
        WHEN @class_desc = 'TYPE' THEN TYPE_NAME(@major_id)
        WHEN @class_desc = 'XML_SCHEMA_COLLECTION' THEN 
            (SELECT name FROM sys.xml_schema_collections
              WHERE xml_collection_id=@major_id)
        WHEN @class_desc = 'MESSAGE_TYPE' THEN 
            (SELECT name FROM sys.service_message_types WHERE message_type_id=@major_id)
        WHEN @class_desc = 'SERVICE_CONTRACT' THEN 
           (SELECT name FROM sys.service_contracts
              WHERE service_contract_id=@major_id)
        WHEN @class_desc = 'SERVICE' THEN
          (SELECT name FROM sys.services WHERE service_id=@major_id)
        WHEN @class_desc = 'REMOTE_SERVICE_BINDING' THEN
          (SELECT name FROM sys.remote_service_bindings
             WHERE remote_service_binding_id=@major_id)
        WHEN @class_desc = 'ROUTE' THEN
          (SELECT name FROM sys.routes WHERE route_id=@major_id)
        WHEN @class_desc = 'FULLTEXT_CATALOG' THEN
          (SELECT name FROM sys.fulltext_catalogs WHERE fulltext_catalog_id=@major_id)
        WHEN @class_desc = 'SYMMETRIC_KEY' THEN
          (SELECT name FROM sys.symmetric_keys WHERE symmetric_key_id=@major_id)
        WHEN @class_desc = 'CERTIFICATE' THEN
          (SELECT name FROM sys.certificates WHERE certificate_id=@major_id)
        WHEN @class_desc = 'ASYMMETRIC_KEY' THEN
          (SELECT name FROM sys.asymmetric_keys WHERE asymmetric_key_id=@major_id)
        WHEN @class_desc = 'SERVER' THEN 
             (SELECT name FROM sys.servers WHERE server_id=@major_id)
        WHEN @class_desc = 'SERVER_PRINCIPAL' THEN SUSER_NAME(@major_id)
        WHEN @class_desc = 'ENDPOINT' THEN 
             (SELECT name FROM sys.endpoints WHERE endpoint_id=@major_id)      
        ELSE '?'
    END
    RETURN @the_entity_name
END;
GO
-- Return server-level permissions for the user.
SELECT class
    ,class_desc
    ,dbo.entity_instance_name(class_desc, major_id) AS entity_name 
    ,minor_id
    ,SUSER_NAME(grantee_principal_id) AS grantee
    ,SUSER_NAME(grantor_principal_id) AS grantor
    ,type
    ,permission_name
    ,state_desc 
FROM sys.server_permissions 
WHERE grantee_principal_id = SUSER_ID('public');
GO
-- Return database-level permissions for the user.
SELECT class
    ,class_desc
    ,dbo.entity_instance_name(class_desc , major_id) AS entity_name 
    ,minor_id
    ,USER_NAME(grantee_principal_id) AS grantee
    ,USER_NAME(grantor_principal_id) AS grantor
    ,type
    ,permission_name
    ,state_desc   
FROM  sys.database_permissions 
WHERE grantee_principal_id = DATABASE_PRINCIPAL_ID('public');
GO

TOP

How do I determine if a column is used in a computed column expression?

Before you run the following query, replace , , and > with valid names.

Transact-SQL

USE ;
GO
SELECT OBJECT_NAME(object_id) AS object_name
    ,COL_NAME(object_id, column_id) AS computed_column 
    ,class_desc
    ,is_selected
    ,is_updated
    ,is_select_all
FROM sys.sql_dependencies
WHERE referenced_major_id = OBJECT_ID('')
    AND referenced_minor_id = COLUMNPROPERTY(referenced_major_id, '', 'ColumnId')
    AND class = 1;
GO

TOP

How do I find all the columns that are used in a computed column expression?

Before you run the following query, replace  with a valid name.

Transact-SQL

USE ;
GO
SELECT OBJECT_NAME(d.referenced_major_id) AS object_name
    ,COL_NAME(d.referenced_major_id, d.referenced_minor_id) AS column_name
    ,OBJECT_NAME(referenced_major_id) AS dependent_object_name 
    ,COL_NAME(d.object_id, d.column_id) AS dependent_computed_column
    ,cc.definition AS computed_column_definition
FROM sys.sql_dependencies AS d
JOIN sys.computed_columns AS cc 
    ON cc.object_id = d.object_id AND cc.column_id = d.column_id AND d.object_id=d.referenced_major_id     
WHERE d.class = 1
ORDER BY object_name, column_name;
GO

TOP

How do I find the columns that depend on a specified CLR user-defined type or alias type?

Before you run the following query, replace  with a valid name and _type_name> with a valid, schema-qualified CLR user-defined type, or schema-qualified alias type name. The following query requires membership in the db_owner role or permissions to see all dependent column and computed column metadata in the database.

Transact-SQL

USE ;
GO
SELECT OBJECT_NAME(object_id) AS object_name 
    ,c.name AS column_name 
    ,SCHEMA_NAME(t.schema_id) AS schema_name
    ,TYPE_NAME(c.user_type_id) AS user_type_name
    ,c.max_length
    ,c.precision
    ,c.scale
    ,c.is_nullable
    ,c.is_computed
FROM sys.columns AS c
INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id
WHERE c.user_type_id = TYPE_ID(''); 
GO

The following query returns a restricted and narrow view of columns dependent on a CLR user-defined type or alias, but the result set is visible to the public role. You can use this query if you have granted REFERENCE permissions on your user-defined type to others and you do not have permission to view the metadata of the objects others have created that use the type.

Transact-SQL

USE ;
GO
SELECT OBJECT_NAME(object_id) AS object_name 
    ,COL_NAME(object_id, column_id) AS column_name
    ,TYPE_NAME(user_type_id) AS user_type
FROM sys.column_type_usages
WHERE user_type_id = TYPE_ID('');
GO

TOP

How do I find the computed columns that depend on a specified CLR user-defined type or alias type?

Before you run the following query, replace  with a valid name and  with a valid, schema-qualified CLR user-defined type, alias type name.

Transact-SQL

USE ;
GO
SELECT OBJECT_NAME(object_id) AS object_name
    ,COL_NAME(object_id, column_id) AS column_name
FROM sys.sql_dependencies
WHERE referenced_major_id = TYPE_ID('')
    AND class = 2 -- schema-bound references to type
    AND OBJECTPROPERTY(object_id, 'IsTable') = 1;   -- exclude non-table dependencies 

TOP

How do I find the parameters that depend on a specified CLR user-defined type or alias type?

Before you run the following query, replace  with a valid name and _name.data_type_name> with a valid, schema-qualified CLR user-defined type, alias type name. The following query requires membership in the db_owner role or permissions to see all dependent column and computed column metadata in the database.

Transact-SQL

USE ;
GO
SELECT OBJECT_NAME(object_id) AS object_name
    ,NULL AS procedure_number
    ,name AS param_name
    ,parameter_id AS param_num
    ,TYPE_NAME(p.user_TYPE_ID) AS type_name
FROM sys.parameters AS p
WHERE p.user_TYPE_ID = TYPE_ID('')
UNION 
SELECT OBJECT_NAME(object_id) AS object_name
    ,procedure_number
    ,name AS param_name
    ,parameter_id AS param_num
    ,TYPE_NAME(p.user_TYPE_ID) AS type_name
FROM sys.numbered_procedure_parameters AS p
WHERE p.user_TYPE_ID = TYPE_ID('')
ORDER BY object_name, procedure_number, param_num;
GO

The following query returns a restricted and narrow view of parameters that depend on a CLR user-defined type or alias, but the result set is visible to the public role. You can use this query if you have granted REFERENCE permissions on your user-defined type to others and you do not have permission to view the metadata of the objects others have created that use the type.

Transact-SQL

USE ;
GO
SELECT OBJECT_NAME(object_id) AS object_name
    ,parameter_id
    ,TYPE_NAME(user_type_id) AS type_name
FROM sys.parameter_type_usages 
WHERE user_type_id = TYPE_ID('');
GO

TOP

How do I find the CHECK constraints that depend on a specified CLR user-defined type?

Before you run the following query, replace  with a valid name and _name.data_type_name> with a valid, schema-qualified CLR user-defined type name.

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(o.schema_id) AS schema_name
    ,OBJECT_NAME(o.parent_object_id) AS table_name
    ,OBJECT_NAME(o.object_id) AS constraint_name
FROM sys.sql_dependencies AS d
JOIN sys.objects AS o ON o.object_id = d.object_id
WHERE referenced_major_id = TYPE_ID('')
    AND class = 2 -- schema-bound references to type
    AND OBJECTPROPERTY(o.object_id, 'IsCheckCnst') = 1; -- exclude non-CHECK dependencies
GO

TOP

How do I find the views, Transact-SQL functions, and Transact-SQL stored procedures that depend on a specified CLR user-defined type or alias type?

Before you run the following query, replace  with a valid name and  with a valid, schema-qualified CLR user-defined type, alias type name.

The parameters defined in a function or procedure are implicitly schema bound. Therefore, parameters that depend on a CLR user-defined type or alias type can be viewed by using the sys.sql_dependenciescatalog view. Procedures and triggers are not schema bound. This means that dependencies between any expression defined in the body of the procedure or trigger and a CLR user-defined type or alias type is not maintained. Schema bound views and schema bound user-defined functions that have expressions that depend on a CLR user-defined type or alias type are maintained in the sys.sql_dependencies catalog view. Dependencies between types and CLR functions and CLR procedures are not maintained.

The following query returns all schema-bound dependencies in views, Transact-SQL functions, and Transact-SQL stored procedures for a specified CLR user-defined type or alias type.

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(o.schema_id) AS dependent_object_schema
  ,OBJECT_NAME(o.object_id) AS dependent_object_name
  ,o.type_desc AS dependent_object_type
  ,d.class_desc AS kind_of_dependency
  ,TYPE_NAME (d.referenced_major_id) AS type_name
FROM sys.sql_dependencies AS d 
JOIN sys.objects AS o
  ON d.object_id = o.object_id
  AND o.type IN ('FN','IF','TF', 'V', 'P')
WHERE d.class = 2 -- dependencies on types
  AND d.referenced_major_id = TYPE_ID('')
ORDER BY dependent_object_schema, dependent_object_name;
GO

TOP

How do I find all the constraints for a specified table?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT OBJECT_NAME(object_id) as constraint_name
    ,SCHEMA_NAME(schema_id) AS schema_name
    ,OBJECT_NAME(parent_object_id) AS table_name
    ,type_desc
    ,create_date
    ,modify_date
    ,is_ms_shipped
    ,is_published
    ,is_schema_published
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT' 
    AND parent_object_id = OBJECT_ID('');
GO

TOP

How do I find all the indexes for a specified table?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT i.name AS index_name
    ,i.type_desc
    ,is_unique
    ,ds.type_desc AS filegroup_or_partition_scheme
    ,ds.name AS filegroup_or_partition_scheme_name
    ,ignore_dup_key
    ,is_primary_key
    ,is_unique_constraint
    ,fill_factor
    ,is_padded
    ,is_disabled
    ,allow_row_locks
    ,allow_page_locks
FROM sys.indexes AS i
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
WHERE is_hypothetical = 0 AND i.index_id <> 0 
AND i.object_id = OBJECT_ID('');
GO

TOP

How do I find all the objects that have a specified column name?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT OBJECT_NAME(object_id)
FROM sys.columns
WHERE name = '';
GO

Or

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(o.schema_id) AS schema_name 
    ,o.name AS object_name
    ,type_desc
FROM sys.objects AS o
INNER JOIN sys.columns AS c ON o.object_id = c.object_id
WHERE c.name = '';
GO

TOP

How do I find all the user-defined tables in a specified database?

Before you run the following query, replace  with a valid name.

Transact-SQL

USE ;
GO
SELECT * 
FROM sys.tables;
GO

TOP

How do I find all the tables and indexes that are partitioned?

Before you run the following query, replace  with a valid name.

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(o.schema_id) AS schema_name
    ,OBJECT_NAME(p.object_id) AS table_name
    ,i.name AS index_name
    ,p.partition_number
    ,rows 
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.partition_schemes ps ON i.data_space_id=ps.data_space_id
INNER JOIN sys.objects AS o ON o.object_id = i.object_id
ORDER BY index_name, partition_number;
GO

TOP

How do I find all the statistics on a specified object?

Before you run the following query, replace  with a valid name and  with a valid table, indexed view, or table-valued function name.

Transact-SQL

USE ;
GO
SELECT name AS statistics_name
    ,stats_id
    ,auto_created
    ,user_created
    ,no_recompute
FROM sys.stats
WHERE object_id = OBJECT_ID('');
GO

TOP

How do I find all the statistics and statistics columns on a specified object?

Before you run the following query, replace  with a valid name and  with a valid table, indexed view, or table-valued function name.

Transact-SQL

USE ;
GO
SELECT s.name AS statistics_name
    ,c.name AS column_name
    ,sc.stats_column_id
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc 
    ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
INNER JOIN sys.columns AS c 
    ON sc.object_id = c.object_id AND c.column_id = sc.column_id
WHERE s.object_id = OBJECT_ID('');
GO

TOP

How do I find the definition of a view?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('');
GO

Or, you can use the OBJECT_DEFINITION function as shown in the following example.

Transact-SQL

USE ;
GO
SELECT OBJECT_DEFINITION (OBJECT_ID('')) AS ObjectDefinition;
GO

TOPhttp://msdn.microsoft.com/en-us/library/ms345522.aspx