create a view for the entire Database with Indexes (Excluding UniqueIdentifier fields) .

Question

Post reply

create a view for the entire Database with Indexes (Excluding UniqueIdentifier fields) .

skb 44459

SSCarpal Tunnel

Points: 4768
More actions
April 26, 2010 at 12:22 pm

#231991

I would like to create a views for the entire Database.
The views must exclude any fields which is Type UniqueIdentifer (Type=36) .
Also when creating a Indexes for View it should exclude any index which uses
UniqueIdentifier field.
The table name and View name should be same.
The index name should also match.
The New schema 'Progress' will hold all the views.
Any help is greatly appreciated.
Thanks
do the script may look like
CREATE VIEW [Progress].[ABOPTS]
WITH SCHEMABINDING AS
SELECT
[ALTNO]
,[ANTBREF]
,[ANTBUCD]
,[ASYMDIR]
,[AUTONFG]
,[BKBIT]
,[BKCOL]
,[BKXSIZE]
,[BKYSIZE]
,[BOFLG]
,[CreateDate]
,[CreatedBy]
,[DRAWSEGS]
,[EXTINFG]
,[FTPERPIX]
,[GRIDFG]
,[HIDEPICKFG]
,[HIGHL]
,[InWorkflow]
,[NoteExistsFlag]
,[RecordDate]
,[SHCPFG]
,[SHNAMFG]
,[SHSEGFG]
,[SIMRTM]
,[STATFRONT]
,[TEXTCOL]
,[TRACECD]
,[TRACEFILE]
,[UpdatedBy]
FROM [dbo].[ABOPTS]
GO
CREATE UNIQUE CLUSTERED INDEX [AdoptIdx] ON [Progress].[ABOPTS]
(
[ALTNO] ASC
) WI

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i had a little trouble reading what you wanted, but i think i got it.

1. you want to create a view for every table in the database, but exclude one data type.

2. you want the views to have the same name as the tables they represent.

for #2, no two objects can have the same name, you'll have to add a prefix or something, like vw_Progress for the Progress table and so on.

for #1, something like this will get you started:

SELECT 'CREATE VIEW vw_' + OBJECT_NAME(object_id) + ' AS SELECT ',stuff(( SELECT ',' + name

FROM sys.columns s2

WHERE s2.object_id= s1.object_id --- must match GROUP BY below

AND s2.user_type_id != 36

ORDER BY object_id,column_id

FOR XML PATH('')

),1,1,'') as [cols]

, ' FROM ' + OBJECT_NAME(object_id)

FROM sys.columns s1

GROUP BY s1.object_id --- without GROUP BY multiple rows are returned

ORDER BY s1.object_id

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

skb 44459 SSCarpal Tunnel Points: 4768 More actions · Answer 2

This will work just fine.

But i would like to create the indexes as well.

Lowell SSC Guru Points: 323518 More actions · Answer 3

skb 44459 (4/26/2010)
This will work just fine.
But i would like to create the indexes as well.

The views will use the existing indexes that are already on the tables; no need to add any duplicate indexes for what already exists on the tables.

you should not automatically add any indexes unless you've identified a real performance enhancement for a query that would benefit from it. an example might be if you have a query with a WHERE LastName='SomeName'; if that query gets called a lot, you might want to add an index on the LastName column of the table.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

Lowell (4/26/2010)
i had a little trouble reading what you wanted, but i think i got it.
1. you want to create a view for every table in the database, but exclude one data type.
2. you want the views to have the same name as the tables they represent.
for #2, no two objects can have the same name, you'll have to add a prefix or something, like vw_Progress for the Progress table and so on.
for #1, something like this will get you started:
SELECT 'CREATE VIEW vw_' + OBJECT_NAME(object_id) + ' AS SELECT ',stuff(( SELECT ',' + name
FROM sys.columns s2
WHERE s2.object_id= s1.object_id --- must match GROUP BY below
AND s2.user_type_id != 36
ORDER BY object_id,column_id
FOR XML PATH('')
),1,1,'') as [cols]
, ' FROM ' + OBJECT_NAME(object_id)
FROM sys.columns s1
GROUP BY s1.object_id --- without GROUP BY multiple rows are returned
ORDER BY s1.object_id

What indexes are you going to create? How are you going to determine what the best indexes are for each view? If the tables referenced in the view already have defined indexes - then those indexes would be used when the view is referenced in your other code.

And to Lowell - you can have the same name for an object if it is in a different schema, which is what it sounds like the OP is trying to do.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs