To get rows from another table that may or may not have a match, you use an OUTER JOIN rather than a "regular" (INNER) JOIN. The columns from the...

Unfortunately, you have to go back before SQL 2005 for this to be a "useful" script, since ALTER DATABASE provided a vastly better way to do this.

I believe that's exactly what the code I posted will do. You can uncomment the PRINT statement to verify the views that are being created.

SET Ansi_Nulls On;

SET Quoted_Identifier On;

GO

ALTER Procedure [dbo].[sp_ViewCreate]

@TableName varchar(100),

@Dist varchar(20)

AS

Set Nocount On;

Declare @SQLQuery AS nvarchar(4000)

Declare @ParamDefinition AS nvarchar(2000)

Declare @TSN AS nvarchar(20)

Declare...

Matt Miller (#4) (8/4/2015)

---

ScottPletcher (8/3/2015)

---

BSharbo (8/3/2015)

---

I can understanding adding an identity key to a table to facilicate joins to other tables.

However in this instance there is only the one table.

I...

ben.brugman (8/4/2015)

---

ChrisM@Work (8/4/2015)

---

ScottPletcher (8/3/2015)

---

You can cancel the identity property by using a union or union all:

select top (0) * into #D from D1 union all select top (0) * from...

You can do it with a single pass thru the item_categories table -- I renamed the tables to reflect their contents. Data set up first, then the main query...

I've actually resorted to having some procs accept a special param value that "tells" the proc just to return the current create table command for its result table. Then...

Use sys.columns rather than the information_schema views.

select *

from sys.columns c

where cast(objectpropertyex(c.object_id, 'BaseType') as varchar(2)) = 'u' and c.is_nullable = 1 and c.is_computed = 0

BSharbo (8/3/2015)

---

I can understanding adding an identity key to a table to facilicate joins to other tables.

However in this instance there is only the one table.

I guess I'm still a...

ben.brugman (8/3/2015)

---

This weekend I have been away from the keyboard. And as often the best idea's occure when away from the keyboard. E.g. on my bike (pushbike) or under the...

Lynn Pettis (7/31/2015)

---

ScottPletcher (7/31/2015)

---

ZZartin (7/31/2015)

---

ben.brugman (7/31/2015)

---

Sean Lange (7/31/2015)

---

You should avoid the sp_ prefix on your procedures. It is reserved for MS and denotes it as a system procedure. The performance...

Main SQL query in the bottom code box. But, I must stress again, to maintain data integrity and drastically help performance, you must:

GET RID OF THE DOPEY AND DANGEROUS...

ZZartin (7/31/2015)

---

ben.brugman (7/31/2015)

---

Sean Lange (7/31/2015)

---

You should avoid the sp_ prefix on your procedures. It is reserved for MS and denotes it as a system procedure. The performance issues are mostly...

Lynn Pettis (7/31/2015)

---

Sean Lange (7/31/2015)

---

I would offer that a prefix of any kind...