How SQL Server saves the tables configuration? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How SQL Server saves the tables configuration?

Author

Topic

allansuperze
Starting Member

4 Posts

Posted - 2004-05-24 : 10:38:17

I'm developing a software that is a tool for building templates of data access functions. What I wanna know is how the sql server2000 uses the system tables to save the db configuration. How and in witch tables these informations are held?
The user should only specify the db and the software shall search all its tables and then their fields to build the template.
If it makes any difference I'm under windows xp and using the enterprise manager and query analyzer to manage and access the db.

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-24 : 10:40:20

SELECT * FROM INFORMATION_SCHEMA.Tables

But what you're trying to do dynamically I would think is not a good idea...

Brett

8-)

allansuperze
Starting Member

4 Posts

Posted - 2004-05-24 : 10:54:18

What do you mean by dynamically? The software will run once and after building the templates it will only run again after some change in the db. If it was what you fought, why don't you thing it a good idea?

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-24 : 11:20:23

OK...but what about the predicates?


USE Northwind
GO

DECLARE @ColList varchar(8000), @SQL varchar(8000), @TABLE_NAME sysname

DECLARE myCursor99 CURSOR 
FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @TABLE_NAME

WHILE @@FETCH_STATUS = 0
  BEGIN
	SELECT @ColList = COALESCE(@ColList + ', ', '') + 
   	  '[' + CAST(COLUMN_NAME AS sysname) + ']'
	  FROM INFORMATION_SCHEMA.Columns 
	 WHERE TABLE_NAME = @TABLE_NAME
	SELECT @SQL = 'DROP PROC [SEL_' + @TABLE_NAME + ']'
	EXEC(@SQL)
--	SELECT @SQL
	SELECT @SQL = 'CREATE PROC [SEL_' + @TABLE_NAME + '] AS SELECT ' + @ColList + ' FROM [' + @TABLE_NAME + ']'
--	SELECT @SQL
	EXEC(@SQL)
	
	SELECT @ColList = Null

	FETCH NEXT FROM myCursor99 INTO @TABLE_NAME
  END

CLOSE  myCursor99
DEALLOCATE myCursor99

It's not 100%....

Brett

8-)

allansuperze
Starting Member

4 Posts

Posted - 2004-05-25 : 08:27:34

Now I'm really confuse

. It's obvious that my SQL knowledge is far behind yours, but I think I understood the querie you made.
I don't access the Internet in home, so, after work, when I got home, I tried to serch for some info in a db. I thought that INFORMATION_SCHEMA was a table of some kind that would be with the info that I need like that
TABLE_NAME - FIELD_NAME - FIELD_TYPE

Table1 - Field1 - Integer
Table1 - Field2 - VarChar
Table2 - Field1 - Integer

with more complex columns of course, but you got the idea.
Well there is no such table in my db, there is only a lot of sys... tables and my owns, so I believe that I miss something or my lack of knowledge make it to hard to understand.
I've not tried the queries you provided me, I will do it today, but could you explain how this information is kept in the db, how Northwind got into the story?
Thanks for the help you are providing me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources