Trying to determine column dependencies.

Stamey (7/19/2011)

My task is to go through certain fields one many DBs and servers to make the columns larger. In this case it is because the standard column size is growing, per HIPAA rules. I need to know if the columns have any dependencies on them that must be removed before we make the field larger. One case would be if the field participates in an index.

I have already written the necessary TSQL to determine if the column participates in an index or is referenced in a proc, but now I'm thinking of other constraints and am looking for code to determine if the column has any dependencies of any kind on it.

It seems that sp_Depends will not work in this situation even if it did work reliably (I have been reading it missis things if the objects weren't created in the correct order), as according to BOL, it doesn't do column dependencies.

The end goal is to take the list of servers, DBs, Schemas, and Columns run them in a temporary table on a given server and generate the necessary scripts to expand the fields as well as drop and recreate any necessary dependent objects along the way.
I have almost 1400 columns to deal with, which is why I am trying to automate this.

Thanks,
Chris

declare @tbl_nme as varchar(50)
declare @col_nme as varchar(50)
declare @level int
set @level = 1
set @tbl_nme='YOURTABLENAME'
set @col_nme= 'YOURCOLUMNNAME'

select
obj.name as obj_nm
, col.name as col_nm
, depobj.name as dep_obj_nm
, CASE depobj.type
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'FOREIGN KEY'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
END as dep_obj_type
, null as dep_col_nm
, @level as level
into #temp
from sysobjects obj
join syscolumns col on obj.id = col.id
left join (sysdepends dep join sysobjects depobj on depobj.id = dep.id)
on obj.id = dep.depid
and col.colid = dep.depnumber
where obj.name = @tbl_nme
and col.name = @col_nme

while (@@rowcount > 0)
begin
set @level = @level + 1
insert into #temp
select
obj.name as obj_nm
, col.name as col_nm
, depobj.name as dep_obj_nm
, CASE depobj.type
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'FOREIGN KEY'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
END as dep_obj_type
, null as dep_col_nm
, @level as level
from sysobjects obj
join syscolumns col on obj.id = col.id
left join (sysdepends dep join sysobjects depobj on depobj.id = dep.id)
on obj.id = dep.depid
and col.colid = dep.depnumber
where exists(select 1 from #temp a where obj.name = a.dep_obj_nm and
col.name = a.dep_col_nm and level = @level - 1 and dep_col_nm is not null)
end

select * from #temp
drop table #temp

/****** Object: Table [dbo].[FieldTest] Script Date: 07/20/2011 09:50:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[FieldTest](
[Field1] [char](16) NOT NULL,
[Field2] [nchar](10) NULL,
[Field3] [varchar](10) NULL,
[Field4] [nvarchar](12) NULL,
CONSTRAINT [PK_FieldTest] PRIMARY KEY CLUSTERED
(
[Field1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/****** Object: Index [Index1] Script Date: 07/20/2011 09:50:23 ******/
CREATE NONCLUSTERED INDEX [INDEX1] ON [dbo].[FieldTest]
(
[Field1] ASC,
[Field3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object: Index [INDEX2] Script Date: 07/20/2011 09:50:23 ******/
CREATE NONCLUSTERED INDEX [INDEX2] ON [dbo].[FieldTest]
(
[Field1] ASC,
[Field2] ASC
)
INCLUDE ( [Field4]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object: Trigger [dbo].[trgFieldTest] Script Date: 07/20/2011 09:50:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: TEST trigger
-- =============================================
CREATE TRIGGER [dbo].[trgFieldTest]
ON [dbo].[FieldTest]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here
Update T
Set T.Field4 = Left(I.Field4, 10)
From FieldTest T
Inner Join inserted I On T.Field1=I.Field1
END

GO

ALTER TABLE [dbo].[FieldTest] WITH NOCHECK ADD CONSTRAINT [CK_FieldTest] CHECK (([Field4]>'a'))
GO

ALTER TABLE [dbo].[FieldTest] CHECK CONSTRAINT [CK_FieldTest]
GO

CREATE VIEW [dbo].[ViewOfFieldTest]
AS
SELECT Field1, Field3
FROM dbo.FieldTest

GO