Locate Foreign keys without Indexes
-
How can i locate tables and their columns that are foreign keys and are not indexed. i have a large number of tables to process
I understand, that Indexes on Foreign Keys can largely improve the performance when there are few inserts or updates.
-
Quick and easy open the database in Enterprise manager and open Query Analyzer make sure the Object browser is open then right click on the table and click on create to generate the create table statement you should see the Foreign keys as constraints. Run a search for constraints in SQL Server BOL (books online). Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie -
I have a set of scripts that writes the indexes and foreign keys to a table which could then be queried to get what you need on a mass scale......
Here:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DBA_OBJECTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DBA_OBJECTS]
GO
CREATE TABLE [dbo].[DBA_OBJECTS] (
[Database] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Object_Type] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[table_name] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[column_name] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[column_id] [smallint] NULL ,
[data_type] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[length] [smallint] NULL ,
[prec] [smallint] NULL ,
[scale] [int] NULL ,
[default_value] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[default_cons_name] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[is_nullable] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[is_identity] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[constraint_name] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[condition] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pos] [smallint] NULL ,
[child_table] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[child_column] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[parent_table] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[parent_column] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[index_name] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[is_unique] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Filegroup] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DBA_Scripts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DBA_Scripts]
GO
CREATE TABLE [dbo].[DBA_Scripts] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[Database] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Object_Type] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CMD_TYPE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Object_name] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[pos] [smallint] NOT NULL ,
[Text] [varchar] (7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DBA_GetIndexes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DBA_GetIndexes]
GO
--DBA_GetIndexes 'NorthWind'
CREATE Proc DBA_GetIndexes
@dbname Varchar(100)
As
Set Nocount On
Declare @Object_type Varchar(512),
@Object_name Varchar(512),
@table_name varchar(512) ,
@Column_id smallint,
@MaxID int,
@column_name varchar(512),
@index_name varchar(512),
@data_type varchar(64),
@length smallint,
@prec smallint,
@scale int,
@is_nullable char(1),
@is_identity char(1),
@Filegroup varchar(50),
@TypeString Varchar(100),
@Child_Table varchar(128),
@Child_column varchar(128),
@Parent_Table varchar(128),
@Parent_column varchar(128),
@SQLTxt Varchar(7000),
@SQLTxt1 Varchar(7000),
@SQLTxt2 Varchar(7000),
@Pos int,
@Header Varchar(1000),
@List1 Varchar(1000),
@Mid Varchar(1000),
@List2 Varchar(1000),
@Trailer Varchar(1000),
@exec_stmt nvarchar(625),
@showdev bit,
@allstatopts int,
@allrelstatopts int,
@allcatopts int,
@name sysname,
@curdbid int,
@cmd varchar(8000),
@bitdesc varchar(35) /* db option English description */
set nocount on
Set @cmd = 'USE ' + @dbName + ' '
Set @cmd =@cmd + 'SELECT ''' + @dbName + ''', ''Index'', tab.name table_name, ind.name index_name, INDEX_COL(tab.name, ind.indid, idk.keyno) column_name
, CASE WHEN ind.status & 0x2 = 0x2 THEN ''Y'' ELSE ''N'' END is_unique, idk.keyno FROM sysindexes ind INNER JOIN sysindexkeys idk ON idk.indid = ind.indid
INNER JOIN sysobjects tab ON idk.id = tab.id AND tab.id = ind.id WHERE NOT (ind.status & 0x800 = 0x800) AND NOT (ind.status & 0x1000 = 0x1000)
AND tab.xtype = ''U'' AND ind.name not like ''_WA%'' ORDER BY 1, 2'
Insert Into DBA_OBJECTS ([Database], Object_Type, table_name , index_name, column_name, is_unique, pos)
Exec (@cmd)
declare IndexList cursor Read_Only for
select distinct table_name ,index_name, pos, column_name,data_type,length,prec,scale,is_nullable,is_identity,[Filegroup]
from DBA_OBJECTS
Where Object_Type = 'Index'
order by table_name, index_name, pos
open IndexList
fetch next
from IndexList
into @table_name , @index_name, @Column_id, @column_name, @data_type,@length,@prec,@scale,@is_nullable,@is_identity,@Filegroup
while @@fetch_status <> -1
begin
If @column_id = 1
Begin
Set @SQLTxt = 'USE ' + @dbName + ' If Not Exists(Select [Name] From sysindexes Where [Name] = ''' + @index_name + ''') '
Select @MaxId = Max(pos) from DBA_OBJECTS
Where index_name = @index_name
Group By index_name
End
Set @SQLTxt = @SQLTxt + Case
When (@column_id = 1) then ' Create Index [' + @index_name + '] ON ' + @dbName + '( [' + @column_name + ']'
When (@column_id > 1 ) then ',[' + @column_name + '] '
END
If (@column_id = @MaxID)
Begin
INSERT INTO [dbo].[DBA_Scripts]([Database],[CMD_TYPE], [Object_Type], [Object_name], [pos], [Text])
VALUES( @DBname,'Drop', 'Index', @table_name, 2, 'USE ' + @dbName + ' If Exists(Select [Name] From sysindexes Where [Name] = ''' + @index_name + ''') Drop Index [' + @table_name + '].[' + @index_name + ']' )
INSERT INTO [dbo].[DBA_Scripts]([Database],[CMD_TYPE], [Object_Type], [Object_name], [pos], [Text])
VALUES( @DBname, 'Add', 'Index', @table_name, 2, @SQLTxt)
End
fetch next
from IndexList
into @table_name , @index_name, @Column_id, @column_name,@data_type,@length,@prec,@scale,@is_nullable,@is_identity,@Filegroup
end
close IndexList
deallocate IndexList
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DBA_GetForeignKeys]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DBA_GetForeignKeys]
GO
--DBA_GetForeignKeys 'NorthWind'
CREATE Proc DBA_GetForeignKeys
@dbname Varchar(100)
As
Set Nocount On
Declare @Object_type Varchar(512),
@Object_name Varchar(512),
@table_name varchar(512) ,
@Column_id smallint,
@MaxID int,
@column_name varchar(512),
@constraint_name varchar(512),
@data_type varchar(64),
@length smallint,
@prec smallint,
@scale int,
@is_nullable char(1),
@is_identity char(1),
@Filegroup varchar(50),
@TypeString Varchar(100),
@Child_Table varchar(128),
@Child_column varchar(128),
@Parent_Table varchar(128),
@Parent_column varchar(128),
@SQLTxt Varchar(7000),
@AFK_CMND Varchar(7000),
@DFK_CMND Varchar(7000),
@Pos int,
@Header Varchar(1000),
@List1 Varchar(1000),
@Mid Varchar(1000),
@List2 Varchar(1000),
@Trailer Varchar(1000),
@exec_stmt nvarchar(625),
@showdev bit,
@allstatopts int,
@allrelstatopts int,
@allcatopts int,
@name sysname,
@curdbid int,
@cmd varchar(8000),
@bitdesc varchar(35) /* db option English description */
set nocount on
Set @cmd = 'USE ' + @dbName + ' '
Set @cmd =@cmd + 'SELECT ''' + @dbName + ''', ''ForKey'', foreignkeycols.child_table,obj.name,foreignkeycols.child_column,foreignkeycols.child_pos,foreignkeycols.parent_table,foreignkeycols.parent_column FROM (
SELECT tab1.name child_table,col1.name child_column,CASE col1.colid WHEN ref.fkey1 THEN 1 WHEN ref.fkey2 THEN 2 WHEN ref.fkey3 THEN 3
WHEN ref.fkey4 THEN 4 WHEN ref.fkey5 THEN 5 WHEN ref.fkey6 THEN 6 WHEN ref.fkey7 THEN 7 WHEN ref.fkey8 THEN 8 WHEN ref.fkey9 THEN 9
WHEN ref.fkey10 THEN 10 WHEN ref.fkey11 THEN 11 WHEN ref.fkey12 THEN 12 WHEN ref.fkey13 THEN 13 WHEN ref.fkey14 THEN 14 WHEN ref.fkey15 THEN 15
WHEN ref.fkey16 THEN 16 END child_pos,tab2.name parent_table,col2.name parent_column,ref.constid constraint_id,CASE col2.colid WHEN ref.rkey1 THEN 1
WHEN ref.rkey2 THEN 2 WHEN ref.rkey3 THEN 3 WHEN ref.rkey4 THEN 4 WHEN ref.rkey5 THEN 5 WHEN ref.rkey6 THEN 6 WHEN ref.rkey7 THEN 7 WHEN ref.rkey8 THEN 8
WHEN ref.rkey9 THEN 9 WHEN ref.rkey10 THEN 10 WHEN ref.rkey11 THEN 11 WHEN ref.rkey12 THEN 12 WHEN ref.rkey13 THEN 13 WHEN ref.rkey14 THEN 14 WHEN ref.rkey15 THEN 15
WHEN ref.rkey16 THEN 16 END parent_pos FROM syscolumns col1 INNER JOIN sysreferences ref ON col1.id = ref.fkeyid INNER JOIN sysobjects tab1 ON
tab1.id = col1.id INNER JOIN syscolumns col2 ON col2.id = ref.rkeyid INNER JOIN sysobjects tab2 ON tab2.id = col2.id
WHERE col1.colid IN (ref.fkey1, ref.fkey2, ref.fkey3, ref.fkey4, ref.fkey5, ref.fkey6, ref.fkey7, ref.fkey8, ref.fkey9, ref.fkey10, ref.fkey11,
ref.fkey12, ref.fkey13, ref.fkey14, ref.fkey15,ref.fkey16) AND col2.colid IN (ref.rkey1, ref.rkey2, ref.rkey3, ref.rkey4, ref.rkey5, ref.rkey6, ref.rkey7,
ref.rkey8, ref.rkey9, ref.rkey10, ref.rkey11, ref.rkey12, ref.rkey13, ref.rkey14, ref.rkey15, ref.rkey16))
foreignkeycols INNER JOIN sysobjects obj ON obj.id = foreignkeycols.constraint_id
WHERE foreignkeycols.child_pos = foreignkeycols.parent_pos ORDER BY 1, 2, 4'
Insert Into DBA_OBJECTS ([Database],[Object_Type],[child_table],[constraint_name],[child_column],[pos],[parent_table],[parent_column])
Exec (@cmd)
declare IndexList cursor scroll for
select distinct Parent_Table , constraint_name, Child_Table, Child_column, Parent_Table, Parent_column, Pos
from DBA_OBJECTS
Where Object_Type = 'ForKey'
order by constraint_name, pos
open IndexList
fetch first
from IndexList
into @table_name , @constraint_name, @Child_Table, @Child_column, @Parent_Table, @Parent_column, @Pos
while @@fetch_status <> -1
begin
Select @MaxId = Max(pos) from DBA_OBJECTS
Where constraint_name = @constraint_name
Group By constraint_name
If @Pos = 1
Begin
Set @Header = 'USE ' + @dbName + ' ALTER TABLE ' + @Child_Table + ' WITH NOCHECK ADD CONSTRAINT '
Set @List1 = ' ' + @Child_column + ''
Set @Mid = ') REFERENCES ' + @Parent_Table
Set @List2 = ' ' + @Parent_column + ''
Set @Trailer = ') '
end
If @Pos > 1
Begin
Set @List1 = @List1 + ', ' + @Child_column + ''
Set @List2 = @List2 + ', ' + @Parent_column + ''
End
If @Pos = @MaxId
Begin
Set @AFK_CMND = @Header + @constraint_name + ' FOREIGN KEY ' + '(' + @List1 + @Mid + '(' + @List2 + @Trailer
Set @DFK_CMND = 'USE ' + @dbName + ' ALTER TABLE ' + @Child_Table + ' DROP CONSTRAINT ' + @constraint_name
INSERT INTO [dbo].[DBA_Scripts]([Database],[CMD_TYPE], [Object_Type], [Object_name], [pos], [Text])
VALUES( @dbName,'DROP', 'ForKey', @constraint_name, 2, @DFK_CMND)
INSERT INTO [dbo].[DBA_Scripts]([Database],[CMD_TYPE], [Object_Type], [Object_name], [pos], [Text])
VALUES( @dbName,'ADD', 'ForKey', @constraint_name, 2, @AFK_CMND)
End
fetch next
from IndexList
into @table_name , @constraint_name, @Child_Table, @Child_column, @Parent_Table, @Parent_column, @Pos
end
Close IndexList
deallocate IndexList
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
This is a must-have for any DBA, so I'am attempting to join the tables
sysindexes
sysindexkeys
sysforeignkeys
sysobjects
to arrive at a elegant and efficient solution.
-
Here is the problem, the previous solution uses too much resources for a simple task and your solution is using Microsoft property the System tables which changes with service packs and versions. The other solution is to use the database Diagramming wizard in Enterprise manager you will see the constraints. Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie -
If all I needed was a picture of them, I am sure I would use what you suggest. However, I need a list of fields and tables as my solution gives, along with drop and rebuild scripts for each object, which I can store, and refer to as builds go through, do compares against, etc. If the origionator of this thread doesn't need this, it can be pared down to fit his needs, or perhaps your solution is better.......I simply offer one solution to be used or discarded as his needs indicate....
And as far as the changing schema's, I've been dealing with that since version 2.0 of Sql Server.......I don't figure it's too much of a bother for what I want to accomplish.....so while I agree that a novice, or someone who doesn't want to be bothered by schema changes may not need something like this, or care to change as server versions change, it's a simple thing for me to accomodate changes as they come along....
-
I am not a novice, Database schema changes one word ALTER and save as .sql. If database diagramming is simple DDL (data definition langauge) writers will not earn very good living doing just that, and Rational Rose, Erwin studio and Visio will not cost more than most software development IDE(integrated development environment).
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie -
I did not mean to imply that you were a novice......
Once you create your .sql files for the foreign keys, and another for your indexes......how do you determine the keys which have no index? I would love to know, so that I do not have to generate the actual tables holding this relationally....
and in all the years I've used the modeling tools you mention, along with many others, I've yet to discover the "show me foreign keys with no corresponding index" command....
-
All information about indexes are in the sysindex table in every database, so you have some options Index tuning wizard, DBCC SHOW_STATISTICS (table_name , index_name), or just plain UPDATE STATISTICS query. The key is database documentation and updating the document with major changes like ALTER table or column that will change your indexing strategies. Today most databases are very busy mine uses little or no resources.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie -
I can't help but still think your missing the point here......which is to identify foreign key fields within a database of maybe hundreds of tables and possibilities which have no index on them currently. No alternative you have shown is capable of doing that. I absolutely agree with the statements you have made:
1. The process I use is resource intensive.
2. Changes to schema will cause me to have to update my scripts.
However, with those things aside, my solution works. Period. None of your statements have answered the actual question of how to do what was asked. No Index tuning wizard, DBCC SHOW_STATISTICS (table_name , index_name), or just plain UPDATE STATISTICS query will ever be able to answer the question.
DO YOU OR DO YOU NOT HAVE AN ALTERNATIVE SOLUTION WHICH ACTUALLY DOES WHAT WAS ASKED?
-
I guess we have to disagree because I gave you those solutions for schema change, long resource using code is usually not needed just get the create table statement and the create index statements for the table, I wrote a tutorial on this topic in 2000. There is a reason for show results in a grid which Oracle still doesnot have and the sysindex table in every database. I have been in a shop with 480 databases if you document everything you find you don't need to run most of those discovery code. Now have a nice day.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie -
QUOTE : "long resource using code is usually not needed just get the create table statement and the create index statements for the table?"
Of course, you are absolutely right......and of course, if that was all that was needing to be done, again you would be right.....however, the code does much more than that, it allows a list of fields which can be queried on to give you the fields from indexes and fields from foreign keys which do not match...the script generation is simply an afterthought, added in for another purpose altogether, which can be stripped out completely if it is not needed...Obviously, you have missed the important part of the process which was shown, and should review the solution in more detail. The purpose of what was presented was NOT to generate schema code, it simply does that in addition. Take time to understand what you are criticizing, prior to doing that.
With the above code, the origional asker of the question can simply take the statements used to get indexes and foreign keys and join them as subqueries for what he needed.....which looks like exactly what he said he would do in fact......
-
Will the following do the job:
SELECT SCHEMA_NAME(o.schema_id) As SchemaName
, o.Name As TableName
, c.Name As ColumnName
FROM sys.objects o INNER JOIN sys.columns c ON o.object_id = c.object_id
INNER JOIN sysforeignkeys fk ON c.object_id = fk.fkeyid And c.column_id = fk.fkey
LEFT OUTER JOIN sysindexkeys ik ON fk.fkeyid = ik.id And fk.fkey = ik.colid
WHERE o.Name <> 'sysdiagrams'
And SCHEMA_NAME(o.schema_id) <> 'sys'
And ik.id Is Null
ORDER BY SchemaName
, TableName
, ColumnName
-
Most efficiently......a very good solution overall....
-
When I try to run this query I get the following error:
Server: Msg 195, Level 15, State 10, Line 1
'SCHEMA_NAME' is not a recognized function name.
Any ideas?
Thanks,
Harley
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply