This example seems very efficient:

DECLARE @Items TABLE (ID INT IDENTITY(1, 1), Item1 CHAR, Item2 CHAR, Grp bigint)

INSERT ...

Try this:

Select C.*, K.Constraint_Name, K.ORDINAL_POSITION as [KEY_POSITION]

From INFORMATION_SCHEMA.COLUMNS C

Left Outer Join INFORMATION_SCHEMA.KEY_COLUMN_USAGE K

ON K.Table_Schema=C.Table_Schema

And K.Table_Name=C.Table_Name

And K.Column_Name=C.Column_Name

And K.Constraint_Name Like 'PK_%'

Order By Table_Schema, Table_Name, Ordinal_Position

I think that this is the "connected subgraphs" problem from my Discrete Algorithms class in college. Of course it was in Pascal, not SQL, and it was 30 years...

Jeffrey Williams (6/22/2008)

---

Yeah, I guess I could see using '*' in that situation - but it still suffers from the same problem. If someone modifies the source table, you...

Glad it worked out for you.

Synonyms can not use Schema-binding nor be referenced by schema-bound objects. That means...

Jeffrey Williams (6/22/2008)

---

In the above, it is mentioned that you should create views to access your linked servers. That is good, but I would go one step further and...

A View consists of a single Select statement, only.

You can often transform procedural code into a single Select in unobvious ways, but this is always very case-specific. So in...

It's "While ...", but I would recommend that you figure out how to write set-based code instead of relying on loop-based techniques. The performance difference is about an order...

It is also possible to automate this process, though that is a bit of work in itself.

This is a duplicate topic. Please do not cross-post.

Please direct all future replies to this topic:http://www.sqlservercentral.com/Forums/FindPost521163.aspx

Here is an example of automating cross-database View creation, off the top of my head:

USE CaseInsensitiveDB;

GO

Declare @sql nvarchar(max)

Set @sql = ''

Select @sql = @sql + '

EXEC(''CREATE VIEW [dbo].[' +...

Can you show us the procedure and the execution plans?

Garfi61416 (6/21/2008)

---

But I would have to do with for every single table in the database, right? The problem is that there are hundreds of tables. Is there a...

Well a Veiw will filter all queries through its SELECT statement. If you need to filter it through a procedure, then you woul dneed the view to SLEECT from...