July 19, 2011 at 11:50 am
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
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
July 19, 2011 at 1:37 pm
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
Let's make a list...I heard:
- indexes
- procs
Brainstorming on other places to look internally:
- user-defined functions
- SQLCLR objects
- column defaults
- column and table check constraints (may contain length checks or other references)
- rules (hopefully you're not still using these)
- indexed views
- regular views with schemabinding
- views that manipulate the column length or do string manipulation or that would otherwise care of a length change
- foreign keys (hopefully none on character-based columns)
- primary keys (same as fks)
Externally (is it a concern?):
- references by remote Linked Servers
- references by embedded sql in applications
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 19, 2011 at 2:13 pm
Only so much of this can be automated. You also need to look for things that reference views or stored procs because there could be a restriction down the line. sys.sql_modules can help find some stuff. You can see if the table name and the column name is in the object definition. If you use * anyplace then you can only find that the table is used.
July 20, 2011 at 6:02 am
I already have code that searches for the column in procs, indexes, and UDFs. My concern is for other objects, such as constraints and primary keys, or rather any other type of object that will cause the field expansion to fail. This is how I came up with the index code. I did not think these fields were indexed in the various DBs so I generated change code for the expansion and when deployment ran it they received a failure because one of the fields participated in an index, and SQL Server would not expand it.
Once I had created the index code I thought about the fact that there could be other types of objects having dependencies on the column so I began researching how to find out what they are. Looks at scripts and articles from here, sys.sql_dependencies, sys.dm_sql_referencing_entities, sys.dm_sql_referenced_entities and the actual code of sp_depends has not given me what I believe I need to get a complete picture of dependencies on each column.
That was the point where I decided to post the question here.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
July 20, 2011 at 6:28 am
Stamey (7/20/2011)
I already have code that searches for the column in procs, indexes, and UDFs. My concern is for other objects, such as constraints and primary keys, or rather any other type of object that will cause the field expansion to fail. This is how I came up with the index code. I did not think these fields were indexed in the various DBs so I generated change code for the expansion and when deployment ran it they received a failure because one of the fields participated in an index, and SQL Server would not expand it.Once I had created the index code I thought about the fact that there could be other types of objects having dependencies on the column so I began researching how to find out what they are. Looks at scripts and articles from here, sys.sql_dependencies, sys.dm_sql_referencing_entities, sys.dm_sql_referenced_entities and the actual code of sp_depends has not given me what I believe I need to get a complete picture of dependencies on each column.
That was the point where I decided to post the question here.
Thanks,
Chris
I have this snippet for checking columns based on references in sysdepends;
i haven't bothere dto update it to use the newer sys.sql_expression_dependencies view for 2005+
this was checking a specific column, and not all columns, but i think this will help:
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
Lowell
July 20, 2011 at 8:17 am
That doesn't pick up the PK dependency. Not sure why.
Also, perhaps the sp_Depends bug, it doesn't pick up the view of the table is created after the view (the likely scenario being that the table was deleted and recreated at some point in time after a given view was created). This is certainly a possibility in my environment.
Now, in this case, if SQL Server won't care about the dependent object when it is expanding the field, I don't care either because it won't affect the scripts I have to generate for deployment. So far, from my testing, it looks like Indexes,
However, since I'm here, and I like a challenge, I want to see if I can get all of this to work as expected.
Here's my test scenario. I believe this script has all of the objects of the table included.
/****** 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
I then ran your script for each field in the table.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
July 20, 2011 at 9:06 am
Stamey, to be sure you're going to have to write all the code by hand. Define the list of objects you need to inspect (what I started to do), figure out how to look at the objects in the metadata and go after each type one by one. The dependency information in SQL Server is not reliable. This is about the best way I have seen it laid out:
There are rumors that all of this is fixed with Denali but we'll have to wait and see if they deliver. Good luck 😎
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 20, 2011 at 10:29 am
Yeah, I know. I did find a TSQL script that will script out a DB and I hope I can modify that to allow me to tell it what to script, depending on what I find in the dependencies, however I have to find out that info.
I just found a few SQL 2000 boxes on the network and now have to modify my Index dependency script to account for those.
It might take some time, but this is gonna be awesome when I'm done.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply