Sql Server - Return Schema for Sysobjects

SQL Server - Return SCHEMA for sysobjects

If you mean SQL Server 2005 or higher, use sys.objects instead of sysobjects:

SELECT  sys.objects.name, sys.schemas.name AS schema_name
FROM    sys.objects 
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id

2005 introduced schemas. up to 2000, users equaled schemas. The same query for SQL Server 2000:

SELECT  sysusers.name AS OwnerName, sysobjects.name
FROM sysobjects
INNER JOIN sysusers ON sysobjects.uid = sysusers.uid

How to get the schema name for sysobjects when querying a sql server database

Maybe this can help you :

SELECT name AS object_name 
  ,SCHEMA_NAME(schema_id) AS schema_name
  ,type_desc
  ,create_date
  ,modify_date
FROM sys.objects
WHERE type = 'P'
ORDER BY name asc;

getting correct schema name

For each database, you can use

SELECT s.name, o.name 
FROM sys.all_objects AS o
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
where o.type = 'u'

or you can use

SELECT   s.name AS schema_name, o.name
FROM    sys.objects o
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
where o.type = 'u'

or you can use

Select schema_Name(schema_id) as [Schema], [name] as [Object]     
From sys.objects o
Where o.type = 'u'

System Table to get Schema Name

You could join to sys.schemas or use the OBJECT_SCHEMA_NAME function.

SELECT OBJECT_SCHEMA_NAME(o.object_id) AS schema_name, o.name AS object_name, o.type_desc AS object_type
FROM sys.objects AS o
ORDER BY schema_name, object_name;

SELECT s.name AS schema_name, o.name AS object_name, o.type_desc AS object_type
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
ORDER BY schema_name, object_name;

Retrieve all tables and views name with schema name from SQL Server

This should work, though it would have been rather easy to find out:

SELECT 
    *
FROM [INFORMATION_SCHEMA].[TABLES]
WHERE [TABLE_TYPE] IN('BASE TABLE', 'VIEW');

How do I get the schema name of the objects in the table sys.all_objects?

Are you talking about system objects such as sp_helptext? You can call sp_helptext and other system objects using multiple formats and these are translated for you (mostly for backward compatibility reasons):

EXEC sp_helptext 'sp_helptext';
EXEC dbo.sp_helptext 'sp_helptext';
EXEC sys.sp_helptext 'sp_helptext';

SELECT * FROM sysobjects;
SELECT * FROM dbo.sysobjects;
SELECT * FROM sys.sysobjects;    

But the object can only belong to one schema. In this case it is sys even if it answers to dbo as well.

If you have user objects that are showing up as sys, please let us know what they are. If you are just trying to track down user objects, perhaps you named something as dbo.foo that also exists under the sys schema. I might suggest sys.objects instead of sys.all_objects if you don't want to return system objects.

EDIT:

And as Ray pointed out, you are passing schema_id to the function. The documentation shows you need to pass object_id, not schema_id. So either do one of the following:

SELECT OBJECT_SCHEMA_NAME(o.[object_id]),* FROM sys.all_objects o;

-- or

SELECT SCHEMA_NAME(o.[schema_id]),* FROM sys.all_objects o;

Personally, I prefer a join, e.g.:

SELECT s.name, o.* FROM sys.all_objects AS o;
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id];

Why? Because this query can work across databases, whereas several of the metadata functions will return NULL or - even worse - the wrong object when called from a different database.

How does one select the server name in a sys objects query

You can use linked servers to select data from databases on two different servers within a single query.

You can set up linked servers through SQL Server Mangement Studio under Server Objects --> Linked Servers. Once you have linked [server1] to [server2] you should be able to execute your query.

---

Related Topics