We help IT Professionals succeed at work.

Getting data out of sql server efficiently

Pete_Burke
Pete_Burke asked
on
Medium Priority
240 Views
Last Modified: 2010-04-16
Hi Experts,

What I have to do is check about 100 tables in sql server to see if there have been any new records added after a specific date and time. Any changes detected must be reported to a front end GUI written in C#, the records will be displayed in a datagrid and later exported to XML.  Each table has a create date time column (Create_DTTM) So I need to loop through all the 100 table names and check if they have any records created after a certain date and time. If they do then select all of the record (select * from table) together with the column names and add it to some kind of "list", ideally inside sql server to aide performance rather than in the GUI application.  Once all of the tables have been checked display all the records in the GUI and then export to XML.  Any ideas on how to do this efficiently and dynamically using stored procedures or some other sql server method? Any suggestions on some kind of list and views on using a datagrid or some thing else. And any top tips on XML would be appreciated.

Regards,

Pete

Comment
Watch Question

Commented:
I am a bit puzzled on how you plan to display records from the different tables in the GUI. I presume the tables have different look and feel (columnnames and types) and therefore giving you a hard time displaying records in the same grid (a GUI with about 100 grids would look a bit crowded :-)  But to your question:

There are at least 3 ways of getting information on available tables in your database:
1. SELECT * FROM INFORMATION_SCHEMA.TABLE WHERE table_type = 'BASE TABLE' AND table_name NOT LIKE 'sys%'
2. SELECT name FROM sysobjects WHERE type = ‘U’
3. EXEC SP_TABLES

The first is preferable since it promises compliance with ANSI standard. If MS internal/system tables changes the INFORMATION_SCHEMA views will change accordingly and your code will still work...

All available information about all columns:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Fully qualified name of all user tables:
SELECT table_catalog + '.' + table_schema + '.' + table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE' AND table_name NOT LIKE 'sys%'

A while ago I found a script similar to what you seek that was written by Erland Sommarskog, SQL Server MVP. It can still be found along with extensive explanations on the topic dynamic sql at http://www.sommarskog.se/dynamic_sql.html  For simpleness I also copied the script (see below). He uses by the way syscolumns access strategy. Obvious the INFORMATION_SCHEMA.COLUMNS can also be used. Choose yourself...
============ Start script ============

DECLARE @tbl    sysname,
        @sql    nvarchar(4000),
        @params nvarchar(4000),
        @count  int

DECLARE tblcur CURSOR STATIC LOCAL FOR
   SELECT object_name(id) FROM syscolumns WHERE name = 'LastUpdated'
   ORDER  BY 1
OPEN tblcur

WHILE 1 = 1
BEGIN
   FETCH tblcur INTO @tbl
   IF @@fetch_status <> 0
      BREAK

   SELECT @sql =
   N' SELECT @cnt = COUNT(*) FROM dbo.' + quotename(@tbl) +
   N' WHERE LastUpdated BETWEEN @fromdate AND ' +
   N'                           coalesce(@todate, ''99991231'')'
   SELECT @params = N'@fromdate datetime, ' +
                    N'@todate   datetime = NULL, ' +
                    N'@cnt      int      OUTPUT'
   EXEC sp_executesql @sql, @params, '20060101', @cnt = @count OUTPUT

   PRINT @tbl + ': ' + convert(varchar(10), @count) + ' modified rows.'
END

DEALLOCATE tblcur

============ End script ============

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.