generate all possible joins in sql server based on relations

Below query generates the SQL Script with all the possible joins in the database based on the existing relationships. this can be used as a handy query to write the scripts .

CREATE procedure pr_schema_delete_ref_rows_7 (@action varchar(20)= 'select') as set nocount on select t1.fkeyid,t1.rkeyid as pk,t1.fkeyid as fk, schema_name(sof.schema_id) as fsc_name,object_name(t1.fkeyid) as fk_tbl,fc.name as fkey_col ,schema_name(sor.schema_id) as rsc_name,object_name(t1.rkeyid) as pk_tbl ,pc.name as pkey_col ,t1.keyno into #sysref from sysforeignkeys t1 join sys.objects sof on sof.object_id = t1.fkeyid join sys.objects sor on sor.object_id = t1.rkeyid -- below join to get column names join sys.columns fc on fc.column_id =t1.fkey  and fc.object_id = sof.object_id join sys.columns pc on pc.column_id  = t1.rkey and pc.object_id = sor.object_id -- below 3 lines is to avoid the problems with self reference left outer join sysreferences t2 on object_name(t1.fkeyid)+object_name(t1.rkeyid) = object_name(t2.rkeyid)+object_name(t2.fkeyid) where t2.fkeyid is null if not exists (select * from #sysref) begin select schema_name(schema_id) as schema_nm,object_name(object_id) from sys.objects where type  = 'u' return end --******************************************************* -- get the max no.of joins foreign keys in a chain declare @n varchar(9); with cte(fkeyid,level)     as      (select fkeyid,0 from sysforeignkeys     union all     select t1.pk ,level+1 from #sysref t1 join cte t2 on t1.fk = t2.fkeyid ) -- statement that executes the cte select @n = max(level)  from cte print @n --******************************************************* declare @v1 varchar(max) set @v1 = ''   declare @v2 varchar(max) set @v2 = '' declare @select varchar(max) set @select = '' declare @orderby varchar(2) set  @orderby = @n+1 DECLARE @OpsStr varchar(4000) set @OpsStr = '' ---------------------------------------------------------------------------------------------------------------------------------------------- while not @n = 0     begin          set @OpsStr = @OpsStr + 'a'+ @n+'.fsc_name+''.''+quotename(object_name('+'a'+ @n+'.fk))  ' + ','         -- set @OpsStr = 'tbl' + @n   --if @v2 > 0 -- to skip join afte the first table column     -- column   --set @select =  @select +@v2 +' as v , ' + @n +' as n ,' +  ' ''join'' as joi , '                 set @select =  @select+    'isnull(a'+ @n+'.fsc_name+''.''+quotename(object_name('+'a'+ @n+'.fk)),'''')  as  tbl' +@n+ ','                                     --set @select =  @select + ''' on '' as on_con , '                 -- join condition                    -------------------------------------------------------------------------------------------------------------------------------------------                 -- join = part                 -- below '=' for join clause and eliminate the join for after 1st table with null majic                 -- if the entire set is null (after first table join) then replace the wholse set with join                 --set @select =  @select                                 + 'isnull('' on ''+ quotename(a'+ @v2+'.rsc_name)+''.''+quotename(object_name('+'a'+ @v2+'.pk)) +''.''+ ' + 'quotename(a'+ @v2+'.pkey_col) '  +'+''=''+'                                         + 'quotename(a'+ @v2+'.fsc_name)+''.''+quotename(object_name('+'a'+ @v2+'.fk)) +''.''+ ' + 'quotename(a'+ @v2+'.fkey_col)  +''  join  '' ,case when isnumeric(a'+ @n+'.fk)     =1 then '' join '' else '''' end)  as  j_condition' +@v2 +','                 -------------------------------------------------------------------------------------------------------------------------------------------                                  set @v1 =  @v1 + ' right outer join #sysref a' +cast(@n as varchar(4)) + ' on a'+ @v2+'.pk =a'+cast(@n as varchar(4))+'.fk'                                      set @v2 = @n set @n = @n -1   continue end ---------------------------------------------------------------------------------------------------------------------------------------------- --*******************************************************         --set @select =  @select +@v2 +' as v , ' + @n +' as n ,' +  ' ''join'' as joi , '     set @select=  @select +  'quotename(a1.rsc_name)+''.''+quotename(object_name(a1.pk)) as roottable' set @select =  @select + ','' on  '' as pk_on_condition , ' -- last pk condition set @select =  @select + 'quotename(a'+ @v2+'.fsc_name)+''.''+quotename(object_name('+'a'+ @v2+'.fk)) +''.''+ ' + 'quotename(a'+ @v2+'.fkey_col)' +'+''=''+' -- pk condition + ' quotename(a1.rsc_name)+''.''+quotename(object_name(a1.pk)) +''.''+quotename(a1.pkey_col)  as  j_condition' +@v2 --+',' ---------------------------------------------------------------------------------------------------------------------------------------------- IF @action = 'DELETE' BEGIN exec ('select '' DELETE from '' as sel,COALESCE('+ @OpsStr +'NULL) + '' FROM'' ,' + @select    +'  from #sysref a ' +@v1 +' order by '+@OpsStr+'1' ) print @OpsStr END ELSE BEGIN exec ('select '' select count(*) from '' as sel,' + @select    +'  from #sysref a ' +@v1 +' order by '+ @orderby ) END