Tuesday, November 15, 2011 5:47 PM
By searching, I've found many pages that repeat the information that a value of L in sys.objects.xtype (or sys.objects.type) means "Log" -- but I've found nothing says what that means.
How does one create an object of type Log? What would such an object do for me?
A similar question seems appropriate concerning the value TT meaning "Table type". How does one create a "table type" object, and why would one do so?
- Edited by J.Merrill Wednesday, November 16, 2011 1:56 PM
Thursday, November 17, 2011 12:57 AMModerator
To be honest, I haven’t found any document about “log” object type in sys.sysobjects. The “log” object type might be a legacy from Sybase database, since prior to version 7.0 the code base for Microsoft SQL Server was sold by Sybase SQL Server to Microsoft. You can find a similar system table on Sybase database: sysobjects.
Moreover, this system table is replaced by sys.objects since from SQL Server 2005, and does not have “log” object type anymore.
If you have feedback for TechNet Subscriber Support, contact email@example.com.
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Thursday, November 17, 2011 4:00 PMI hadn't noticed that Log wasn't documented for sys.objects -- so "it's not relevant any more" sounds like the right answer. But "Table Type" IS documented for sys.objects, and I can't figure out what that might be. The concept of variables and parameters of type "table" is well known -- but having an object in sys.objects that is "Table Type" is not familiar. Does that mean that you can store the definition of a table (its column specs) as a persistent object, and e.g. declare a table variable or parameter based on that? That might be a useful feature but the only info that I've seen is that "Table Type" is defined as an object type in sys.objects. Can you help with that?
Thursday, November 17, 2011 6:37 PM
Log might be an carry-over from the pre-7 days (as suggested) when the transaction log actually was implemented as a table (syslogs).
As for table, yes as of 2008, we have table types, which you use for type for param in stored procedure so you can pass a table into a stored procedure. Such are strongly typed, so you define these as types in the database:
CREATE TYPE myTableType AS Table(c1 int)
Tibor Karaszi, SQL Server MVP | web | blog
- Marked As Answer by J.Merrill Friday, November 18, 2011 5:25 PM
Friday, November 18, 2011 5:42 PM
Thank you for that. I had made local variables of type table, with the definition being "inline" --
declare @tbl table(a int)
I had not realized that you apparently cannot do
create proc x @t table(a int) as select * from @t
-- that is, I had never written a stored proc that accepts a table variable as a parameter.
It is apparently only possible to write a stored proc that accepts a table if you've used "create type". It's odd that nothing I've read today about stored proc (and function) parameters and table variables -- trying to learn what I'd obviously been missing for years -- has mentioned "create type".
I did find this just now, and I suppose that should have pointed me to "create type" -- [quote] You can use a user-defined table type to declare a table-valued parameter as to a parameter for a Transact-SQL stored procedure. [/quote].
I believe this falls into the category "you learn something new every day."
I tend to write application code and usually call stored procs (written by others) from there. AFAIK there is no way -- certainly not with ODBC -- to send a "table" from application code. The best we can do is to send XML data and relationalify it somehow, or use OpenRowset(BULK ...), or do something to split a string ... etc. Again, thanks very much for the answer.
- Edited by J.Merrill Monday, November 21, 2011 4:44 PM
Saturday, November 19, 2011 8:04 AMThe API's were updated with SQL Server 2008 to allow sending a table from a client app. Here's what my search found for ODBC, for instance: http://msdn.microsoft.com/en-us/library/bb522663.aspx
Tibor Karaszi, SQL Server MVP | web | blog
Tuesday, November 22, 2011 4:13 PMUnfortunately, I can't yet presume existence of SQL Server 2008. (I'm still not certain that sys.objects -- rather than sysobjects -- is going to exist at every client; sigh.)