I may have gone through the BOL thousands of times looking for various xtypes. But this time had a closer look at xtype = 'L' Object type. Can be one of these object types: C = CHECK constraint D = Default or DEFAULT constraint F = FOREIGN KEY constraint L = Log FN = Scalar function IF = Inlined table-function P = Stored procedure PK = PRIMARY KEY constraint (type is K) RF = Replication filter stored procedure S = System table TF = Table function TR = Trigger U = User table UQ = UNIQUE constraint (type is K) V = View X = Extended stored procedure What is xtype = 'L'. Just don't have any idea what is this? I hv never seen this. Does anybody here know this?
In sysobjects table it is valid as per your reference and other xtype is valid to syscolumns & systypes system table. Based on the user defined data type the simplest level, a record is the physical storage associated with a table or index row. This is applicable to the columns that are for LOB data types (text, ntext, image etc.) Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
XTYPE varies on 3 system tables, obviously for sysobject its for Log and others it is for type of UDF used. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Data types such as 'bigint','decimal','float','int','money','numeric ','real','smallint','smallmoney','tinyint' in addition to LOB data types I have referred above Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Satya, Looking at the xtype column, it classifies various types of objects - tables, views, indexes, constraints, functions, procedures, triggers, and logs. Different system tables have an xtype column, and the 'L' value on the column does not seem to be valid on all system tables. But I can't quite follow how you go from the LOB data types, to the "log" object type. Educate me!
My referene is for systypes and sycolumns, not specific to sysobjects. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br />I believe we wait until Chetan comes back and say where and what he wants to clarify.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
Ah - now I get it ... I think ... There is just one list of values for xtype in BOL, and this is on the entry for sysobjects, but actually the 'L' value can only occur in systypes and syscolumns.
If you search on XTYPE then you would get reference on other 2 system tables too. SYSOBJECTS reference for L is correct as of Log. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
You can find the references, yes, but there's no list of possible values there - only under sysobjects.
True, I was referring until Kalen Delaney book and other available resources at my end. I believe this is a good quote to pickup to post under MVP newsgroups for further calrification. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
I believe it is left over from SQL Server 6.5... Becuase in sql server 6.5 and before database log file was stored in the database not as .ldf file it was called syslog or something like that... MohammedU. Moderator SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Hello All, actually we would be doing an entire export of database and then would be importing it into a new db. hence, i wanted to ensure we are not missing any objects. Log is just something which aroused a question in my mind. I thank all for posting your ideas on this one.. Thanks, Chetan
Hmm, you come with a simple question for a major discussion here [<img src='/community/emoticons/emotion-1.gif' alt='' />]. Anyways I have shooted the other XTYPE=L based to SQL Dev.team.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
