Solved

Getting data out of sql server efficiently

Posted on 2006-10-26
1
221 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

0
Comment
Question by:Pete_Burke
1 Comment
 
LVL 8

Accepted Solution

by:
redpipe earned 500 total points
ID: 17817484
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 ============
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Bit flags and bit flag manipulation is perhaps one of the most underrated strategies in programming, likely because most programmers developing in high-level languages rely too much on the high-level features, and forget about the low-level ones. Th…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now