Database

indian143 wrote:

May be I have to do like you said

USE [Northwind]
DECLARE @listStr VARCHAR(MAX)
;WITH Source AS
(
	-- Generate list of all the columns from the table
	-- each preceded by our chosen table alias
	SELECT 
	CASE WHEN TABLE_NAME = 'Orders' THEN 'O.'
		 WHEN TABLE_NAME = 'Customers' THEN 'C.'
		 WHEN TABLE_NAME = 'Employees' THEN 'E.'
		 WHEN TABLE_NAME = 'Order Details' THEN 'OD.'
		 ELSE ''
		 END + COLUMN_NAME AS ColName
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME IN ('Orders', 'Customers','Employees','Order Details')
	AND TABLE_SCHEMA='dbo'
)
-- generate a comma-separated list of those columns
SELECT @listStr = COALESCE(@listStr+',' ,'') + ColName
FROM Source
-- build the rest of the SQL statement
DECLARE @SQL varchar(max)
SET @SQL = 'SELECT ' + @listStr + ' FROM Orders O '
SET @SQL = @SQL + 'JOIN Customers C on O.CustomerID=C.CustomerID '
SET @SQL = @SQL + 'JOIN Employees E on O.EmployeeID=E.EmployeeID '
SET @SQL = @SQL + 'JOIN [Order Details] OD on OD.OrderID = O.OrderID '
-- use PRINT rather than select as it is easier to copy
PRINT @SQL

SELECT C.CustomerID,C.CompanyName,C.ContactName,C.ContactTitle,C.Address,C.City,C.Region,
C.PostalCode,C.Country,C.Phone,C.Fax,
E.EmployeeID,E.LastName,E.FirstName,E.Title,E.TitleOfCourtesy,E.BirthDate,
E.HireDate,E.Address,E.City,E.Region,
E.PostalCode,E.Country,E.HomePhone,E.Extension,E.Photo,E.Notes,E.ReportsTo,E.PhotoPath,
OD.OrderID,OD.ProductID,OD.UnitPrice,OD.Quantity,OD.Discount,
O.OrderID,O.CustomerID,O.EmployeeID,O.OrderDate,O.RequiredDate,O.ShippedDate,
O.ShipVia,O.Freight,O.ShipName,
O.ShipAddress,O.ShipCity,O.ShipRegion,O.ShipPostalCode,O.ShipCountry 
FROM Orders O 
JOIN Customers C on O.CustomerID=C.CustomerID 
JOIN Employees E on O.EmployeeID=E.EmployeeID 
JOIN [Order Details] OD on OD.OrderID = O.OrderID 

CREATE TABLE [dbo].[AuditLog](
    [AuditID] [INT] IDENTITY(1,1) NOT NULL,
    [Action] <a href="1">CHAR</a> NULL,
    [TableName] <a href="128">VARCHAR</a> NULL,
    [PrimaryKeyField] <a href="1000">VARCHAR</a> NULL,
    [PrimaryKeyValue] <a href="1000">VARCHAR</a> NULL,
    [FieldName] <a href="500">VARCHAR</a> NULL,
    [OldValue] <a href="8000">VARCHAR</a> NULL,
    [NewValue] <a href="8000">VARCHAR</a> NULL,
    [ModifiedDate] [DATETIME] NULL DEFAULT (GETDATE()),
    [UserName] <a href="200">VARCHAR</a> NULL
) ON [PRIMARY]