Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Here is something that might help you. I have created two user stored procs that do something similar to what you are asking. </p> <ol> <li><p>usp_depends2 - an extended version of sp_depends</p></li> <li><p>usp_FindReferences - this one uses usp_depends2 to find all references for a column in a table (I think this is what you need)</p></li> </ol> <hr> <pre><code> /****** Object: StoredProcedure [dbo].[usp_depends2] Script Date: 11/18/2009 11:55:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[usp_depends2] --- 1996/08/09 16:51 @objname nvarchar(776) /* the object we want to check */ as declare @objid int /* the id of the object we want */ declare @found_some bit /* flag for dependencies found */ declare @dbname sysname /* ** Make sure the @objname is local to the current database. */ DECLARE @sp_depends_xref table ( reftype char(2) , dep_name nvarchar(256) , type char(16) , updated char(7) , selected char(8) , [column] nvarchar(128)) select @dbname = parsename(@objname,3) if @dbname is not null and @dbname &lt;&gt; db_name() begin raiserror(15250,-1,-1) return (1) end /* ** See if @objname exists. */ select @objid = object_id(@objname) if @objid is null begin select @dbname = db_name() raiserror(15009,-1,-1,@objname,@dbname) return (1) end /* ** Initialize @found_some to indicate that we haven't seen any dependencies. */ select @found_some = 0 set nocount on /* ** Print out the particulars about the local dependencies. */ if exists (select * from sysdepends where id = @objid) begin raiserror(15459,-1,-1) INSERT INTO @sp_depends_xref ( refType , dep_name , type , updated , selected , [column]) select 'TO', 'name' = (s6.name+ '.' + o1.name), type = substring(v2.name, 5, 16), updated = substring(u4.name, 1, 7), selected = substring(w5.name, 1, 8), 'column' = col_name(d3.depid, d3.depnumber) from sysobjects o1 ,master.dbo.spt_values v2 ,sysdepends d3 ,master.dbo.spt_values u4 ,master.dbo.spt_values w5 --11667 ,sysusers s6 where o1.id = d3.depid and o1.xtype = substring(v2.name,1,2) collate database_default and v2.type = 'O9T' and u4.type = 'B' and u4.number = d3.resultobj and w5.type = 'B' and w5.number = d3.readobj|d3.selall and d3.id = @objid and o1.uid = s6.uid and deptype &lt; 2 select @found_some = 1 end /* ** Now check for things that depend on the object. */ if exists (select * from sysdepends where depid = @objid) begin raiserror(15460,-1,-1) INSERT INTO @sp_depends_xref ( RefType , dep_name , type) select distinct 'BY', 'name' = (s.name + '.' + o.name), type = substring(v.name, 5, 16) from sysobjects o, master.dbo.spt_values v, sysdepends d, sysusers s where o.id = d.id and o.xtype = substring(v.name,1,2) collate database_default and v.type = 'O9T' and d.depid = @objid and o.uid = s.uid and deptype &lt; 2 select @found_some = 1 end /* ** Did we find anything in sysdepends? */ if @found_some = 0 raiserror(15461,-1,-1) SELECT reftype , dep_name , type , updated , selected , [column] FROM @sp_depends_xref set nocount off return (0) -- sp_depends GO </code></pre> <hr> <pre><code> /****** Object: StoredProcedure [dbo].[usp_FindReferences] Script Date: 11/18/2009 11:55:05 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[usp_FindReferences] -- Add the parameters for the stored procedure here @tablename nvarchar(500) = 0, @colname nvarchar(500) = 0 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; create table #tempTableDependencies ( reftype nvarchar(20), dep_name nvarchar(500), type nvarchar(500), updated nvarchar(500), selected nvarchar(500), col nvarchar(500) ) insert into #tempTableDependencies execute usp_depends2 @tablename create table #tempDependencies ( reftype nvarchar(20), dep_name nvarchar(500), type nvarchar(500), updated nvarchar(500), selected nvarchar(500), col nvarchar(500) ) declare @tempFilteredDependencies table ( objectname nvarchar(500), reftype nvarchar(20), dep_name nvarchar(500), type nvarchar(500), updated nvarchar(500), selected nvarchar(500), col nvarchar(500) ) DECLARE @loopcounter INT select @loopcounter = COUNT(*) FROM #tempTableDependencies DECLARE @dependencyname nvarchar(500) WHILE @loopcounter &gt; 0 BEGIN SELECT TOP 1 @dependencyname = dep_name FROM #tempTableDependencies print 'loop_counter = ' + CAST(@loopcounter as nvarchar(20)) print 'dependency = ' + @dependencyname insert into #tempDependencies execute usp_depends2 @dependencyname insert into @tempFilteredDependencies select @dependencyname as objectname, * from #tempDependencies where col = @colname and dep_name like '%' + @tablename delete from #tempDependencies delete from #tempTableDependencies where dep_name = @dependencyname SET @loopcounter = @loopcounter - 1 END select * from @tempFilteredDependencies drop table #tempDependencies drop table #tempTableDependencies END GO </code></pre> <hr>
    singulars
    1. This table or related slice is empty.
    1. POHow to find data table column reference in stored procedures
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USNir
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to find data table column reference in stored procedures
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload