Display row value as a TABLE column using SQL select statement

Requirement:

Summarize PIFAmt column divide by Summarize Factors column by StateName column and diplay prior & current year data as a table column by auto and property business values.

Table Structure:






Table Data:






You could avoid the following errors when you execute the enclosed SQL select statement and display the result as I explained in my requirement.

Errors:

Server: Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Server: Msg 130, Level 15, State 1, Line 3
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

SQL Select Statement:

SELECT STATENAME,
SUM(CASE WHEN EFFYEAR = 2006 AND BUSINESS = 'AUTO' THEN PIFAMT END) /
SUM(CASE WHEN FACTORS > 0 AND EFFYEAR = 2006 AND BUSINESS = 'AUTO' THEN FACTORS END) AS AUTO_PIF_PREV_YR,
SUM(CASE WHEN EFFYEAR = 2007 AND BUSINESS = 'AUTO' THEN PIFAMT END) /
SUM(CASE WHEN FACTORS > 0 AND EFFYEAR = 2007 AND BUSINESS = 'AUTO' THEN FACTORS END) AS AUTO_PIF_CURR_YR,
SUM(CASE WHEN EFFYEAR = 2006 AND BUSINESS = 'PROPERTY' AND SEGMENT = 'HOMEOWNERS' THEN PIFAMT END) /
SUM(CASE WHEN FACTORS > 0 AND EFFYEAR = 2006 AND BUSINESS = 'PROPERTY' AND SEGMENT = 'HOMEOWNERS' THEN FACTORS END) AS PROP_PIF_PREV_YR,
SUM(CASE WHEN EFFYEAR = 2007 AND BUSINESS = 'PROPERTY' AND SEGMENT = 'HOMEOWNERS' THEN PIFAMT END) /
SUM(CASE WHEN FACTORS > 0 AND EFFYEAR = 2007 AND BUSINESS = 'PROPERTY' AND SEGMENT = 'HOMEOWNERS' THEN FACTORS END) AS PROP_PIF_CURR_YR
FROM FACT
WHERE BUSINESS IN ('AUTO', 'PROPERTY') AND EFFYEAR >= 2006 AND
SEGMENT = (CASE WHEN BUSINESS = 'AUTO' THEN SEGMENT ELSE 'HOMEOWNERS' END)
GROUP BY STATENAME

GO

Results:

User-defined data type dependencies

MSSQLServer has got sp_depends system sp to display the dependencies for a stored procedure, table and view. sp_depends does not support to display the User-defined data type dependencies. For custom database, we will create user defined data types (UDDT) and add this UDDT to a column/variable in a table/stored procedure. This sp (UDTDepends) will help to display the User-defined data type dependencies in a database for a Tables and Stored Procedure.

Compile this sp in a database and execute the same. It will display the dependencies for the user defined data types if it exists in a database.

Criticize are welcome.

if exists (select * from sysobjects where id = object_id(N'[dbo].[UDTDepends]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UDTDepends]
GO

Create Proc UDTDepends
As

SET NOCOUNT ON

SELECT so.name As "ObjectName", sc.name As "ColumnName", type_name(st.xusertype) As "UserDefinedDataType",
type_name(sc.xtype) As "DataType", st.length As "DataLength"

FROM sysobjects so, syscolumns sc, systypes st

WHERE so.xtype In ('U', 'P') and sc.id = so.id and sc.xusertype = st.xusertype and
sc.usertype Not In (0, 1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 19, 20, 21, 22, 23, 24, 80)

ORDER BY so.xtype desc, so.name, st.name

SET NOCOUNT OFF

Stabilize Index

This sp is used to update index in a database. This sp has got three optional varchar parameters.
First parameter will take the tablename. Second parameter will take the indexname. Third parameter will take the fillfactor.

The following option we can execute the sp in a user database.

1. exec StabilizeIndex
It will update all index in a user database

2. StabilizeIndex 'TableName'
It will update all index in a table

3. StabilizeIndex 'TableName', 'IndexName'
It will update an index in a table.

4.StabilizeIndex 'TableName', 'IndexName', ‘FillFactor’
It will update an index in a table with fill factor.

Note: This is not used in system database.

/* To Execute the sp in various options
--StabilizeIndex
--StabilizeIndex 'TableName'
--StabilizeIndex 'TableName', 'IndexName'
--StabilizeIndex 'TableName', 'IndexName', 'FillFactor'
*/
if exists (select * from sysobjects where id = object_id(N'[dbo].[StabilizeIndex]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[StabilizeIndex]
GO

Create Proc StabilizeIndex
(
@TableName Varchar(100) = '',
@IndexName Varchar(100) = '',
@FillFactor Varchar(3) = ''
)
As

Declare @TableID Int, @ObjectName Varchar(100), @IndName Varchar(100), @SQL Varchar(8000)

SET NOCOUNT ON

IF Len(@TableName) = 0
Begin
DECLARE table_cursor CURSOR FOR
SELECT id, name
FROM sysobjects
WHERE xtype = 'U'
ORDER BY name
End
Else
Begin
DECLARE table_cursor CURSOR FOR
SELECT id, name
FROM sysobjects
WHERE xtype = 'U' and id = object_id(@TableName)
ORDER BY name
End

OPEN table_cursor

FETCH NEXT FROM table_cursor
INTO @TableID, @ObjectName

WHILE @@FETCH_STATUS = 0
BEGIN

IF Len(@IndexName) = 0
Begin
DECLARE index_cursor CURSOR FOR
SELECT name
FROM sysindexes
WHERE id = @TableID and indid > 0
End
Else
Begin
DECLARE index_cursor CURSOR FOR
SELECT name
FROM sysindexes
WHERE id = @TableID and name = @IndexName and indid > 0
End
OPEN index_cursor
FETCH NEXT FROM index_cursor INTO @IndName

WHILE @@FETCH_STATUS = 0
BEGIN
IF Len(@FillFactor) = 0
Begin
Set @SQL = 'DBCC DBREINDEX (' + @ObjectName + ',' + @IndName + ')'
End
Else
Begin
Set @SQL = 'DBCC DBREINDEX (' + @ObjectName + ',' + @IndName + ',' + @FillFactor + ')'
End

Exec (@SQL)

FETCH NEXT FROM index_cursor INTO @IndName

END

CLOSE index_cursor
DEALLOCATE index_cursor

FETCH NEXT FROM table_cursor
INTO @TableID, @ObjectName
END

CLOSE table_cursor
DEALLOCATE table_cursor

SET NOCOUNT OFF

ClearData

We can use this sp to clear data in tables. I mainly created this sp for two reason. 1.This is useful to commence the testing from scratch. 2.Whenever we ask the database backup from our customer. They hesitant to give the production database because of confidential. I have used Delete and Truncate statement to clear the data. Please refer SQL Books online difference of Delete and Truncate statement.

/* To Execute the sp
--exec ClearTableData
*/
if exists (select * from sysobjects where id = object_id(N'[dbo].[ClearTableData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ClearTableData]
GO

Create Proc ClearTableData
As

Declare @TableName Varchar(1000), @Reference Varchar(4), @SQL Varchar(8000), @ObjectName Varchar(8000),
@TabName Varchar(1000)

SET NOCOUNT ON

If db_name() In ('master', 'model', 'msdb', 'tempdb')
Begin
Print 'system database are not allowed to clear'
return 0
End

Set @ObjectName = ''
Set @TabName = ''

-- Delete data in Child Object and its Parent Object
Begin
DECLARE ClearTableData_Cursor CURSOR FOR
Select SO.Name "ChildObject",
Case When (Select Count(*) From SysReferences Where rkeyid = SO.id) = 0 Then 'N' Else 'Y' End As Ref
From SysObjects SO, SysReferences SR, SysObjects SO1
Where SO.id = SR.fkeyid And SO.xtype = 'U' And SO1.ID = SR.rkeyid
Order By SO1.Name Desc, SO.Name
OPEN ClearTableData_Cursor

FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName, @Reference

WHILE @@FETCH_STATUS = 0
BEGIN
If @Reference = 'Y'
Begin
-- Concatenate Primary Key Table name
Set @ObjectName = @ObjectName + ',' + @TableName
End
Else If @Reference = 'N'
Begin
Begin
-- Clear data from Foreign Key Tables(Child object)
Set @SQL = ''
Set @SQL = 'Truncate Table [' + @TableName + ']'
exec (@SQL)
End
End

FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName, @Reference
END

CLOSE ClearTableData_Cursor
DEALLOCATE ClearTableData_Cursor

If Len(@Reference) > 0
Begin
-- Clear data for Concatenated Primary Key Tables
Set @ObjectName = Right(@ObjectName,Len(@ObjectName)-1)

While Len(@ObjectName) > 0
Begin
If (CHARINDEX(',',@ObjectName) > 0)
Begin
Set @TabName = Substring(@ObjectName, 1, CharIndex(',',@ObjectName)-1)
Set @ObjectName = SUBSTRING(@ObjectName, Len(@TabName) + 2, Len(@ObjectName))
End
Else
Begin
Set @TabName = @ObjectName
Set @ObjectName = ''
End
Begin
Set @SQL = ''
Set @SQL = 'DELETE From [' + @TabName + ']'
exec (@SQL)
End
End
End
End
--Delete Data in Standalone Parent Object
Begin
DECLARE ClearTableData_Cursor CURSOR FOR
Select Distinct SO.Name "ChildObject"
From SysObjects SO
Where (SO.id In (Select rkeyid From SysReferences))
And SO.xtype = 'U'
Order By SO.Name
OPEN ClearTableData_Cursor

FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
Begin
Set @SQL = ''
Set @SQL = 'DELETE From [' + @TableName + ']'
exec (@SQL)
End

FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName
END

CLOSE ClearTableData_Cursor
DEALLOCATE ClearTableData_Cursor
End
-- Delete data for BaseDate/Master Table
Begin
DECLARE ClearTableData_Cursor CURSOR FOR
Select Distinct SO.Name "ChildObject"
From SysObjects SO
Where (SO.id Not In (Select rkeyid From SysReferences) And SO.id Not In (Select fkeyid From SysReferences))
And SO.xtype = 'U'
Order By SO.Name

OPEN ClearTableData_Cursor

FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

Begin
Set @SQL = ''
Set @SQL = 'Truncate Table [' + @TableName + ']'
exec (@SQL)
End

FETCH NEXT FROM ClearTableData_Cursor
INTO @TableName
END

CLOSE ClearTableData_Cursor
DEALLOCATE ClearTableData_Cursor
End
SET NOCOUNT OFF

Convert Unique Identifier to Varchar data type

SET QUOTED_IDENTIFIER OFF

DECLARE @ObjectID Int, @ObjectName Varchar(100),
@EmptyString Char(1), @IsDefaultBound Char(1),
@ExecuteScript Varchar(8000), @IndexName Varchar(256),
@Loop Int

set nocount on

Create Table #TableInformation (ObjectID Int Not Null, ObjectName Varchar(100) Not Null,
Parent_ObjectID Int Not Null, Parent_ObjectName Varchar(100) Not Null,
ObjectType Char(2) Not Null)
-- IsRowGuid Char(1) Not Null, IsDefaultBound Char(1) Null)
DECLARE csrTableName CURSOR FOR
SELECT SO.ID, SO.Name
FROM SysObjects SO
WHERE SO.xType = 'U'
ORDER BY SO.Name

Set @EmptyString = ''

OPEN csrTableName

FETCH NEXT FROM csrTableName
INTO @ObjectID, @ObjectName

PRINT @EmptyString

WHILE @@FETCH_STATUS = 0
BEGIN

-- 36 = Unique Identifier data type

--Generate Script to Drop Foreign Constraint & Select statement will generate Foreign Key ID, Foreign Key Name,
--Parent Object ID & Name for Foreign Key and Object Type As "FK"
Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType)
Select Constid, Object_Name(Constid), fkeyid, Object_Name(fkeyid), 'C'
From SysForeignKeys Where rkeyID = @ObjectID
Union
Select Constid, Object_Name(Constid), fkeyid, Object_Name(fkeyid), 'C'
From SysForeignKeys Where fkeyID = @ObjectID

--Generate Script to Drop Primary and Unique Constraint & Select statement will generate Primary Key ID,
--Primary Key Name, Parent Object ID & Name for Primary Key and Object Type As "PK"
Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType)
Select ID, Object_Name(ID), @ObjectID, @ObjectName, 'C'
From SysObjects Where Parent_Obj = @ObjectID And xType In ('PK', 'UQ')

--Generate Script to Default Constraint & Select statement will generate Parent Object ID, Default Column Name &
--Parent Object ID & Name for Default Column and Object Type As "D"
Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType)
Select cDefault, Object_Name(SC.cDefault), @ObjectID, @ObjectName, 'C'
From SysColumns SC, SysComments SCM
Where SC.ID = @ObjectID And SCM.ID = SC.cDefault And ObjectProperty(SC.cDefault, 'IsDefaultCnst') = 1 And
SC.xType = 36

--Generate Script to Drop ROWGUID & Select statement will generate RowGUID Column ID, RowGUID Column Name &
--Parent Object ID & Name for RowGUID Column and Object Type As "RG"
Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType)
Select ID, Name, @ObjectID, @ObjectName, 'RG'
From SysColumns Where ID = @ObjectID And Number = 0 And ColumnProperty(@ObjectID, Name, 'IsRowGuidCol') = 1

--Generate Script to Alter Column to Varchar(38) & Select statement will generate UniqueIdentifier Type ID,
--UniqueIdentifier Column Name, Parent Object ID & Name for UniqueIdentifier Column and Object Type As "UI"
Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType)
Select xType, Name, ID, Object_Name(ID), 'UI'
From SysColumns Where ID = @ObjectID And xType = 36

--Generate Script to UnBind Defaults for ID Column & Select statement will generate Parent Object ID,
--Default Column Name & Parent Object ID & Name for Default Column and Object Type As "DF"
Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType)
Select @ObjectID, SC.Name, @ObjectID, @ObjectName, 'DF'
From SysColumns SC, SysComments SCM
Where SC.ID = @ObjectID And SCM.ID = SC.cDefault And ObjectProperty(SC.cDefault, 'IsConstraint') = 0 And
SC.xType = 36

Declare csrIndexName CURSOR FOR
Select Name From SysIndexes
Where ID = @ObjectID

OPEN csrIndexName

FETCH NEXT FROM csrIndexName
INTO @IndexName

WHILE @@FETCH_STATUS = 0
BEGIN
Set @Loop = 0
While @Loop < 10
Begin
IF Exists (Select SC.Name, SC.ID From SysColumns SC, SysIndexes SI
Where SC.ID = SI.ID And SI.ID = @ObjectID And
SI.Name = @IndexName And SC.xtype = 36 And SI.Name Not Like '%_WA_%' And
SC.Name = Index_col(Object_Name(@ObjectID), SI.indid,@Loop))
Begin
--Generate Script to drop Index & Select statement will generate Parent Object ID of the Index Column,
--IndexName, Parent Object ID & Name of the Table Name, and Object Type As "I"
Insert Into #TableInformation(ObjectID, ObjectName, Parent_ObjectID, Parent_ObjectName, ObjectType)
Select @ObjectID, @IndexName, @ObjectID, @ObjectName, 'I'
Break
End
Set @Loop = @Loop + 1
End

FETCH NEXT FROM csrIndexName
INTO @IndexName
END

CLOSE csrIndexName
DEALLOCATE csrIndexName

FETCH NEXT FROM csrTableName
INTO @ObjectID, @ObjectName
END

CLOSE csrTableName
DEALLOCATE csrTableName

PRINT @EmptyString

Select * From #TableInformation Order By ObjectType

PRINT @EmptyString

DECLARE csrScript CURSOR FOR


Select Case When ObjectType = 'DF' Then
'If Exists (Select ID From SysColumns Where ID = Object_ID("[' + Parent_ObjectName + ']") And Name = "' + ObjectName + '" And cDefault > 0 )' + Char(13) +
'Exec sp_unbindefault "[' + Parent_ObjectName + '].' + ObjectName + '"' + Char(13) --+ 'GO'

When ObjectType = 'RG' Then
'If Exists (Select ID From SysColumns Where ID = Object_ID("[' + Parent_ObjectName + ']") And Number = 0 And
Name = "' + ObjectName + '" And ColumnProperty(Object_ID ("[' + Parent_ObjectName + ']")' + ', Name, "IsRowGuidCol") = 1 )' + Char(13) +
'ALTER TABLE [' + Parent_ObjectName + '] ALTER COLUMN [' + ObjectName + '] DROP ROWGUIDCOL' + Char(13) --+ 'GO'

When ObjectType = 'C' Then
'If Exists (Select ID From SysObjects Where ID = Object_ID("[' + ObjectName + ']") And Parent_Obj = Object_Id("[' + Parent_ObjectName + ']"))' + Char(13) +
'ALTER TABLE [' + Parent_ObjectName + '] DROP CONSTRAINT [' + ObjectName + ']' + Char(13) --+ 'GO'

When ObjectType = 'I' Then
'If Exists (Select ID From SysIndexes Where Name = "' + ObjectName + '" And ID = Object_Id("[' + Parent_ObjectName + ']"))' + Char(13) +
'DROP INDEX [' + Parent_ObjectName + '].' + ObjectName + + Char(13) --+ 'GO'

When ObjectType = 'UI' Then
'ALTER TABLE [' + Parent_ObjectName + '] ALTER COLUMN [' + ObjectName + '] Varchar(38)' + Char(13) --+ 'GO'

End As "Script"
From #TableInformation Order By ObjectType

Set @ExecuteScript = ''

OPEN csrScript

FETCH NEXT FROM csrScript
INTO @ExecuteScript

PRINT @EmptyString

WHILE @@FETCH_STATUS = 0
BEGIN
--Print (@ExecuteScript)
Exec (@ExecuteScript)
FETCH NEXT FROM csrScript
INTO @ExecuteScript
END

CLOSE csrScript
DEALLOCATE csrScript

Drop Table #TableInformation

set nocount off

GO

DisplayColumnNames

This sp will return the column names for a table(s) and form a "Select Statement" for a table(s). We can pass a table name as a parameter to display a "Select Statement" for that table.

if exists (select * from sysobjects where id = object_id(N'[dbo].[spDisplayColumnName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spDisplayColumnName]
GO
Create Proc spDisplayColumnName (@TableName Varchar(100)='') As
--Exec spDisplayColumnName 'Account'
--Or
--Exec spDisplayColumnName
Declare @ColumnName Varchar(8000), @TableID Int

Set Nocount On

If Len(@TableName) = 0
Begin
DECLARE DisplayColumnName_Cursor CURSOR FOR
Select SO.ID From SysObjects SO Where xType = 'U' Order By SO.Name
End
Else
Begin
If Not Exists(Select 1 From SysObjects Where ID = Object_ID(@TableName) And xType = 'U')
Begin
Print 'Passed parameter [' + @TableName + '] is not an User table'
Return 0
End
Else
Begin
DECLARE DisplayColumnName_Cursor CURSOR FOR
Select SO.ID From SysObjects SO Where ID = Object_ID(@TableName)
And xType = 'U' Order By SO.Name
End
End

OPEN DisplayColumnName_Cursor

FETCH NEXT FROM DisplayColumnName_Cursor
INTO @TableID

WHILE @@FETCH_STATUS = 0
Begin
Set @ColumnName = ''
Select @ColumnName = @ColumnName + ', ' +Name
From SysColumns Where ID = @TableID
Order By Name
Print Object_Name(@TableID)
Select 'Select ' + Right(@ColumnName,Len(@ColumnName)-1) + ' From ' + Object_Name(@TableID)

FETCH NEXT FROM DisplayColumnName_Cursor
INTO @TableID
End
CLOSE DisplayColumnName_Cursor
DEALLOCATE DisplayColumnName_Cursor

Set Nocount Off
Return 0

SQL Server: Execution Plans

When I started using SQL Server, I was not using the “Show Execution Plan” properly to analyse the query. I always thought that when I compose a query, it is the best it can be; I never even think of performance on my query and I did not give much importance to looking at the “Execution Plan” on my query. In the initial days of my career, I was happy to retrieve the data, not even knowing how it was returned to me and what sort of “Execution Plan” was used on the query. I presumed SQL Server would handle the query performance. I think it is the nature for software engineers, when starting out, or when first learning new technology, not to take the time to learn everything they really need to know before writing a code. Perhaps this is because of competition and immaturity in the IT field.

Days are starting moving and data has been growing on the database file. On one fine day, my customer was not happy with the performance on the query using the application. He has come to me with an unhappy face stating that he was spending extra time to finish his office work due to the slowness of the queries. Initially, I told the customer to increase the system resources, like increase the hard disk in the machine as a temporary solution. Albeit, hard disk cost is cheap but it is not the permanent solution for the performance degradation in the query. He agreed in half-mind that he will do the required things from his side but he asked me to re-look and fine-tune the queries for a permanent solution instead of suggesting he keep on increasing the system resources. I have to consider his personal opinion because customer satisfaction is important in the IT industry. I have promised to my customer that I will re-look and fine-tune the queries.

HOW?

In the initial days of my career, I know the basic things in MS-SQL Server. To be frank, I was not having any idea on my mind whilst doing promise to my customer. But, I personally felt that I would do something to achieve the task with the help of “GOOGLE” and “BOL”. Thanks to “GOOGLE” and “BOL”.

I was reading MS-SQL books, BOL help and searching on websites. I have heard and crossed the concept of “Show Execution Plan”. We can set this option ON using SQL Query Analyzer. Show Execution Plan is an important graphical tool that enables the developer and DBA to analyse, assist and optimise the query and improve the performance of the query.

Show Execution Plan displays different icons for a different task. I am mainly interested on “Table Scan”, “Index Scan” & “Index Seek” & “Clustered Index Scan” & “Clustered Index Seek” icons in this article. May be I could be writing in my future article on other icons.

As the days and years have moved along like a formula 1 race car, I have decided that it is time for me to fully understand how “Table Scan”, “Index Scan” & “Index Seek” & “Clustered Index Scan” & “ClusteredIndex Seek” icons work.

Now, I am ready to analyse the queries and upgrade the performance on my queries. Before analysing the queries, a few questions have been raised in my mind.

When did MS-SQL Server use “Table Scan”?

When did MS-SQL Server use “Index Scan”?

When did MS-SQL Server use “Index Seek”?

When did MS-SQL Server use “Clustered Index Scan”?

When did MS-SQL Server use “Clustered Index Seek”?

I am mainly concern that in what bases MS-SQL Server would use one of this option to analyse the query. I now have taken that time, and here is what I learned. This information should be useful to new developers and DBAs. I have decided to write this article to share my knowledge to help other to get better idea on these methods.

If you like, you can read this article as is, or in front of a SQL Server, following along with my exercises.

DependencyDetails

This sp will return the dependency details of the select table or all tables as a resultset. This resultset will
display the dependency details if it is used in
1. select * from table or
2. insert/delete/update dml statement or
3. select col1, col2 from table.

if exists (select * from sysobjects where id = object_id(N'[dbo].[DependencyDetails]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DependencyDetails]
GO

Create Proc DependencyDetails (@TableName As Varchar(100)='')
As

SET NOCOUNT ON

Declare @MinTableID Int, @MaxTableID Int

IF Len(@TableName) = 0
Begin
Select @MinTableID = Min(ID), @MaxTableID = Max(ID) From SysObjects Where xType = 'U'
End
Else IF Len(@TableName) > 0
Begin
IF Exists (Select ID From SysObjects Where Name = @TableName And xType = 'U')
Begin
Select @MinTableID = ID, @MaxTableID = ID
From SysObjects Where Name = @TableName And xType = 'U'
End
Else
Begin
Print 'Passed parameter [' + @TableName + '] is not an User Table'
Return 0
End
End
Select
Distinct SO.Name, SO1.Name "Referred Object", 'Select *' As "Used for"
From
SysDepends SD, SysObjects SO, SysObjects SO1
Where
SO.ID = SD.DepID And
SelAll = 1 And
SD.ID = SO1.ID And
SD.DepID >= @MinTableID And SD.DepID <= @MaxTableID
Union
Select
Distinct SO.Name, SO1.Name "Referred Object", 'Insert/Update/Delete' As "Used for"
From
SysDepends SD, SysObjects SO, SysObjects SO1
Where
SO.ID = SD.DepID And
SD.ResultObj = 1 And
SD.ID = SO1.ID And
SD.DepID >= @MinTableID And SD.DepID <= @MaxTableID
Union
Select
Distinct SO.Name, SO1.Name "Referred Object", 'Selected Columns' As "Used for"
From
SysDepends SD, SysObjects SO, SysObjects SO1
Where
SO.ID = SD.DepID And
SD.ReadObj = 1 And
SD.ID = SO1.ID And
SD.DepID >= @MinTableID And SD.DepID <= @MaxTableID
Order By SO.Name

SET NOCOUNT OFF

Replace Object Owner

This SP is used to identify and replace the owner of the database objects. This SP will give display object id,
object name, owner id, owner name and concatenate object + owner name with brackets. SP does change the owner for the following objects. SP should available in "master" database to execute in more than one database.

--$History: $ sp_ReplaceObjectOwner
--------------------------------------------------------------------------------------------------------------------------------------------
-- Drop Procedure
--------------------------------------------------------------------------------------------------------------------------------------------

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_ReplaceObjectOwner' AND type = 'P')
DROP PROCEDURE sp_ReplaceObjectOwner
GO

--------------------------------------------------------------------------------------------------------------------------------------------
-- Create procedure
--------------------------------------------------------------------------------------------------------------------------------------------

CREATE PROC sp_ReplaceObjectOwner As

--------------------------------------------------------------------------------------------------------------------------------------------
-- OBJECT NAME: sp_ReplaceObjectOwner
--------------------------------------------------------------------------------------------------------------------------------------------
-- AUTHOR: Vijayakumar G
--------------------------------------------------------------------------------------------------------------------------------------------
-- CREATED: 04-04-2005
--------------------------------------------------------------------------------------------------------------------------------------------
/* DESCRIPTION: This SP is used to identify and replace the owner of the database objects. This SP will give display object id,
object name, owner id, owner name and concatenate object + owner name with brackets. SP does change the owner for the following objects.
SP should available in "master" database to execute in more than one database.
1. Table
2. Stored Procedures
3. Function
4. View
We can later add / modify the filter condition */
--------------------------------------------------------------------------------------------------------------------------------------------
-- PARAMETERS: None
--
--------------------------------------------------------------------------------------------------------------------------------------------
-- RETURNS: 0 - success
--
-- returns from SQL Server
--------------------------------------------------------------------------------------------------------------------------------------------

-- VERSION: $Revision: $ UPDATED: $Modtime: $
--------------------------------------------------------------------------------------------------------------------------------------------
set nocount on

DECLARE @Object_ID Int, @Object_Name Sysname,
@Owner_ID Int, @Owner_Name Sysname, @Owner_Object_Name Sysname

Declare @New_Owner_Name Sysname
Set @New_Owner_Name = 'dbo'
DECLARE CUR_ReplaceObjectOwner CURSOR FOR

Select
SO.ID As "Object ID", SO.Name As "Object Name",
SO.UID As "Owner ID", SU.Name As "Owner Name",
'[' + SU.Name + '].' + '[' + SO.Name + ']' As "Owner + Object Name"
From
SysObjects SO, SysUsers SU
Where
SO.UID = SU.UID And
SO.xType In ('U','P', 'FN','V') And
SO.Name Not Like '%dt_%' And
SO.Name Not In ('syssegments', 'sysconstraints')
Order By xType


OPEN CUR_ReplaceObjectOwner

FETCH NEXT FROM CUR_ReplaceObjectOwner
INTO @Object_ID, @Object_Name, @Owner_ID, @Owner_Name, @Owner_Object_Name

WHILE @@FETCH_STATUS = 0
BEGIN
If @New_Owner_Name != @Owner_Name
exec sp_changeobjectowner @Owner_Object_Name, @New_Owner_Name
If @@Error != 0
Begin
Return 1
End
-- Get the next author.
FETCH NEXT FROM CUR_ReplaceObjectOwner
INTO @Object_ID, @Object_Name, @Owner_ID, @Owner_Name, @Owner_Object_Name
END

CLOSE CUR_ReplaceObjectOwner
DEALLOCATE CUR_ReplaceObjectOwner

set nocount off
return 0

GO