SQL Server 2005 Express - generate script to create VIEW based on relational tables?

Question

Is there a way in SQL Server 2005 Express to generate a script that would create a VIEW based on tables primary/foreign key relationships?

I have multiple databases with thousands of tables, and its very time consuming to look at the "table dependencies" and try to JOIN data in the query window.

Thanks

Answer 1

This Query return Create statement for tables, but you must first note that: 1. only works for 1 column foreign key references 2. has not been tested for sql server express 2005, but works fine for Sql server 2005

create function dbo.func59C217D64BC54EA0B841BF1AB43D9398(@table1 nvarchar(1000), @table2 nvarchar(1000))
returns nvarchar(max)
as
begin
declare @sql nvarchar(max)
set @sql = ''
select @sql = @sql + dr + '.[' +cc +'] AS ['+ cc+ ISNULL(rr,'') + '],'
from (
select column_name cc,
dr = case when table_schema + '.' +table_name = @table1 then 'a' else 'b' end,
cast(NULLIF(row_number() over (partition by column_name order by table_name),1) as nvarchar) rr
 from INFORMATION_SCHEMA.COLUMNS where table_schema + '.' +table_name in
(@table1,@table2)
) i

return substring(@sql,1,len(@sql) - 1)
end


GO
select cast(
'CREATE VIEW ['+r.CONSTRAINT_SCHEMA+'].[vw' +cp.TABLE_NAME+cf.TABLE_NAME+ ']
AS SELECT '+dbo.func59C217D64BC54EA0B841BF1AB43D9398(cp.TABLE_SCHEMA+'.' +cp.TABLE_NAME,cf.TABLE_SCHEMA+ '.' +cf.TABLE_NAME)+' FROM ['+cp.TABLE_SCHEMA+'].['+cp.TABLE_NAME+'] AS a
JOIN ['+cf.TABLE_SCHEMA+'].['+cf.TABLE_NAME +'] AS b
ON a.['+cp.COLUMN_NAME+'] = b.['+cf.COLUMN_NAME +']' as ntext) as sql
 from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS r
 JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cf ON r.CONSTRAINT_NAME = cf.CONSTRAINT_NAME 
 JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cp ON r.UNIQUE_CONSTRAINT_NAME = cP.CONSTRAINT_NAME 


GO
DROP function dbo.func59C217D64BC54EA0B841BF1AB43D9398

hope I helped, or at least make you started