getting records from database

[ajdesigns]

hi
is there any way i could get a multiple records from a whole database (not just 1 table)eg: item no. Where the result set would tell me which table it was in.I work on a very large database (approx 1500 tables) the front end is entirely form based. The forms are setup from numerous fields from numerous tables, sending the data input via the form to the relevent table within the database.Looking at the form setup I cannot see which table the form is sending the input to.
So i want a query that will say
select all fields from the database with a title eg: item_no. Ideally I would not want null values returned.So I would get all tables with item_no in them that contained data returned.

 

[JarlH]

No.

You'll have to write a stored procedure / application that dynamically creates one SELECT statements for each table containing a column named item_no.

 

[dreameR25]

Try this :

DECLARE @TableName nvarchar(60)
DECLARE @SqlCommand nvarchar(4000)

CREATE TABLE #Return (TableName nvarchar(60), [RowCount] int)

-- Get the names of all tables owned by 'dbo', excluding 'Shadow' tables
DECLARE c_tbl CURSOR FAST_FORWARD FOR
SELECT	 a.[name] 
FROM	sysobjects as a
inner join syscolumns as b
	on a.[id] = b.[id]
WHERE	a.xtype = 'U'
AND	a.uid = 1 -- owner is 'dbo'
and	b.name =  'item_no' --'item_no'			
group by a.[name]
ORDER BY a.[name]

-- Dynamically create Sql to retrieve row counts
SET NOCOUNT ON

OPEN c_tbl

FETCH NEXT FROM c_tbl 
INTO @TableName

WHILE (@@fetch_status = 0)
BEGIN
	SET @SqlCommand = 'SELECT @TableName, count(*) FROM dbo.[' + @TableName + ']'

	INSERT #Return (TableName, [RowCount])
	EXEC sp_executesql @SqlCommand, N'@TableName nvarchar(60)', @TableName

	FETCH NEXT FROM c_tbl 
	INTO @TableName
END

CLOSE c_tbl
DEALLOCATE c_tbl

SET NOCOUNT OFF

-- Report all rowcounts
SELECT * FROM #Return

DROP TABLE #Return