SCHEma's, views and base tables

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

Import/Export (DTS) and Replication (2000)

SCHEma's, views and base tables

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-16 : 13:16:53

I was trying to generate format cards for 1100 tables (see script below)...and I was getting "duplicate" data...I was flipping out...it all looked good...then I found the following:


TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE
OHM		OHMAdmin	ABPECNT		BASE TABLE
OHM		OHMEAP		ABPECNT		VIEW

Why would anyone do that? And I'm not sure, but (and I'll have to look this up) but isn't a schema a collection of db objects?

This is a 3rd party product called OHM

And a followup...what would you do with image and text columns, if you had to bcp a table out, modify that data, and reimport all the data...I was going to truncate the tables and reload them...but that doesn't seem possible...updates?(shudder)

Anyway, here's the format file generator...not yet tested...but it looks good (sans image and text problem mentioned):

EDIT: I Thought I fixed th dups...but they're still coming out..begs the question with schemas...how does bcp know what to grab?


SELECT * FROM (
    SELECT '7.0' AS FORMAT_CARD
	 , TABLE_NAME, null AS COLUMN_NAME, 1 AS SQLGroup, 1 AS RowGrouping
      FROM INFORMATION_SCHEMA.Tables
     WHERE TABLE_TYPE = 'BASE TABLE' and table_name = 'ABPECNT'
 UNION ALL
    SELECT CONVERT(varchar(5),MAX(ORDINAL_POSITION)) AS FORMAT_CARD
	 , c.TABLE_NAME, null AS COLUMN_NAME, 2 AS SQLGroup, 1 AS RowGrouping
      FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
        ON c.TABLE_NAME = t.TABLE_NAME 
     AND TABLE_TYPE = 'BASE TABLE'
where c.table_name = 'ABPECNT'
  GROUP BY c.TABLE_NAME
 UNION ALL
    SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLCHAR'+CHAR(9)+'0'+CHAR(9)+CONVERT(VARCHAR(5),
		CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') 	THEN CHARACTER_MAXIMUM_LENGTH
		     WHEN DATA_TYPE = 'int' 					THEN 14
		     WHEN DATA_TYPE = 'smallint' 				THEN 7
		     WHEN DATA_TYPE = 'tinyint' 				THEN 3
		     WHEN DATA_TYPE = 'bit' 					THEN 1
										ELSE 26
		END)
	 + char(9)+'""'+char(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+COLUMN_NAME AS FORMAT_CARD
	 , c.TABLE_NAME, null AS COLUMN_NAME, 3 AS SQLGroup, ORDINAL_POSITION AS RowGrouping
      FROM INFORMATION_SCHEMA.Columns c 
INNER JOIN INFORMATION_SCHEMA.Tables t
        ON c.TABLE_NAME = t.TABLE_NAME 
      AND TABLE_TYPE = 'BASE TABLE' 
     WHERE ORDINAL_POSITION < (SELECT MAX(ORDINAL_POSITION) 
				FROM INFORMATION_SCHEMA.Columns i
			       WHERE i.TABLE_NAME = c.TABLE_NAME)
 and c.table_name = 'ABPECNT'   
and table_type = 'base table'

UNION ALL
   SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLCHAR'+CHAR(9)+'0'+CHAR(9)+CONVERT(VARCHAR(5),
		CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') 	THEN CHARACTER_MAXIMUM_LENGTH
		     WHEN DATA_TYPE = 'int' 					THEN 14
		     WHEN DATA_TYPE = 'smallint' 				THEN 7
		     WHEN DATA_TYPE = 'tinyint' 				THEN 3
		     WHEN DATA_TYPE = 'bit' 					THEN 1
										ELSE 26
		END)
	 + char(9)+'"\r\n"'+char(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+COLUMN_NAME AS FORMAT_CARD
	 , c.TABLE_NAME, null AS COLUMN_NAME, 4 AS SQLGroup, 1 AS RowGrouping
      FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
        ON c.TABLE_NAME = t.TABLE_NAME 
       AND TABLE_TYPE = 'BASE TABLE'
     WHERE ORDINAL_POSITION = (SELECT MAX(ORDINAL_POSITION) 
				FROM INFORMATION_SCHEMA.Columns i
			       WHERE i.TABLE_NAME = c.TABLE_NAME)
and c.table_name = 'ABPECNT'
)AS XXX    
ORDER BY TABLE_NAME, COLUMN_NAME, SQLGroup, RowGrouping

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-16 : 13:32:37

What's a format card?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-16 : 13:49:07

There goes my db2 tripping over my sql...

a-hem...
a format file...

(It's a load card in db2)

Anyway..just found out that the schema is also stored at the column level...why? I don't know...

anyway I also found out that the schema is really the "owner"(?) No?

Every other place I've been the owner has been dbo...so not taking the scchema all this time has bee danderous...

When I finish the scipt I'll post it is scipts..

albiet it's for fixed width files...

Still need to figure out about image and text in bcp out/in..

Thanks for the response tara...

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-16 : 13:50:36

Oh, also..if a table or view can have the same name across schema's...is the same true within a schema?

Lord I hope not...

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-16 : 14:05:04

Yes, the schema column represents the owner. You can not have two objects named the same thing using the same schema or owner.

Tara

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-09-16 : 22:38:21

Brett,

It is VERY handy for your Middle and UI code..

The code base stays the same but the DB is dishing up different data depending on the current users permission...

DavidM

"SQL-3 is an abomination.."

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-17 : 09:54:35

OK...but the view is created as :


CREATE VIEW OHMEAP.ABPECNT AS SELECT * FROM OHMAdmin.ABPECNT

What does that buy them?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources