Error schema does not exist - Исправление ошибок и поиск оптимальных решений проблем

Remove From My Forums

Question
I created two schemas inside the same database by right-clicking the Security node and selecting New -> Schema. They are both owned by dbo.

In SSMS new query window I am able to create tables (using T-SQL) making the table owned by one of these schemas:

CREATE TABLE MYDB.SCHEMA1.CATS …

but when I try to do the same with the other schema it is not allowed:

CREATE TABLE MYDB.SCHEMA2.DOGS …

In SSMS new query window, the word SCHEMA2 is underlined in red and when I hover the mouse on it, an error message appears saying that the schema does not exist in the current database or I don’t have the permission to use it.

I am confused because I created these schemas using the identical procedure, one right after the other.

P.S. I first tried the names ALL and GLOB (intending to make a schema that contained tables that were company-wide rather than limited to a specific application) and then thought maybe these names were reserved words and that was the source of the error.
But then I created the second schema with the name EALL and that had the same error.

How do I see who has permission to use a schema?
- Edited by
  
  Wednesday, October 17, 2012 5:32 PM

Answers

Hi,

We can check the table name under Object Explorer, if you created table “SCHEMA1.EMPLOYEES” like following:
```
create table [SCHEMA1.EMPLOYEES]
```
This table will be created under the default schema “dbo” and its name will be “dbo.SCHEMA1.EMPLOYEES” under Object Explorer. If you want to create table «EMPLOYEES» under “SCHEMA1”, please refer to the following method:
```
create table SCHEMA1.EMPLOYEES
```
Regarding the SQL Server Management Studio Intellisense, this issue can occur due to local cache, you can use the following steps to refresh local cache.

Open SQL Server Management Studio, click Edit -> IntelliSense -> Refresh Local Cache

Allen Li

TechNet Community Support
- Proposed as answer by
  Allen Li — MSFT
  Friday, October 26, 2012 2:31 AM
- Marked as answer by
  Allen Li — MSFT
  Monday, October 29, 2012 1:17 AM
It would appear that you made a mistake in your tsql query that created the second table. Since you did not post the actual statement, we can only guess but this is the most obvious source. Perhaps you accidentally used:

create table [SCHEMA1.EMPLOYEES] …
or
create table «SCHEMA1.EMPLOYEES» …

Intellisense will sometimes «adjust» what you are typing in unexpected ways if you are not paying close attention.
- Proposed as answer by
  Allen Li — MSFT
  Friday, October 26, 2012 2:31 AM
- Marked as answer by
  Allen Li — MSFT
  Monday, October 29, 2012 1:16 AM

Источник

Question
I get the following error when creating stored procedure:

Msg 2797, Level 16, State 1, Procedure SearchAllTables, Line 90
The default schema does not exist.

***********************************************

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN

CREATE TABLE #Results(TableName nvarchar(370), KeyValues nvarchar(3630), ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
,@TableShortName nvarchar(256)
,@TableKeys nvarchar(512)
,@SQL nvarchar(3830)

SET @TableName = »
SET @SearchStr2 = QUOTENAME(‘%’ + @SearchStr + ‘%’,»»)

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = »

— Scan Tables
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE’
AND QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)
), ‘IsMSShipped’
) = 0
)
Set @TableShortName=PARSENAME(@TableName, 1)
— print @TableName + ‘;’ + @TableShortName +’!’ — *** DEBUG LINE ***

— LOOK Key Fields, Set Key Columns
SET @TableKeys=»
SELECT @TableKeys = @TableKeys + »» + QUOTENAME([name]) + ‘: » + CONVERT(nvarchar(250),’ + [name] + ‘) + »’ + ‘,’ + »’ + ‘
FROM syscolumns
WHERE [id] IN (
SELECT [id]
FROM sysobjects
WHERE [name] = @TableShortName)
AND colid IN (
SELECT SIK.colid
FROM sysindexkeys SIK
JOIN sysobjects SO ON
SIK.[id] = SO.[id]
WHERE
SIK.indid = 1
AND SO.[name] = @TableShortName)
If @TableKeys<>»
SET @TableKeys=SUBSTRING(@TableKeys,1,Len(@TableKeys)-8)
— Print @TableName + ‘;’ + @TableKeys + ‘!’ — *** DEBUG LINE ***

— Search in Columns
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’, ‘nvarchar’)
AND QUOTENAME(COLUMN_NAME) > @ColumnName
) — Set ColumnName

IF @ColumnName IS NOT NULL
BEGIN
SET @SQL=’
SELECT
»’ + @TableName + »’
,’+@TableKeys+’
,»’ + @ColumnName + »’
,LEFT(‘ + @ColumnName + ‘, 3630)
FROM ‘ + @TableName + ‘ (NOLOCK) ‘ +
‘ WHERE ‘ + @ColumnName + ‘ LIKE ‘ + @SearchStr2
—Print @SQL — *** DEBUG LINE ***
INSERT INTO #Results
Exec (@SQL)
END — IF ColumnName
END — While Table and Column
END —While Table

SELECT TableName, KeyValues, ColumnName, ColumnValue FROM #Results
END

Answers

- Proposed as answer by
  
  Thursday, April 24, 2014 2:50 AM
- Marked as answer by
  Khurj01
  Thursday, April 24, 2014 7:20 PM

Here is the code from my blog post:

CREATE PROCEDURE spSearchStringInTable
(@SearchString NVARCHAR(MAX),
 @Table_Schema sysname,
 @Table_Name sysname)
 AS
 BEGIN
DECLARE @Columns NVARCHAR(MAX), @Cols NVARCHAR(MAX), @PkColumn NVARCHAR(MAX)

-- Get all character columns
SET @Columns = STUFF((SELECT ', ' + QUOTENAME(Column_Name) 
 FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')
 AND TABLE_NAME = @Table_Name 
 ORDER BY COLUMN_NAME 
 FOR XML PATH('')),1,2,'')

IF @Columns IS NULL -- no character columns
   RETURN -1

-- Get columns for select statement - we need to convert all columns to nvarchar(max)
SET @Cols = STUFF((SELECT ', cast(' + QUOTENAME(Column_Name) + ' as nvarchar(max)) as ' + QUOTENAME(Column_Name)
 FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')
 AND TABLE_NAME = @Table_Name 
 ORDER BY COLUMN_NAME 
 FOR XML PATH('')),1,2,'')
 
 SET @PkColumn = STUFF((SELECT N' + ''|'' + ' + ' cast(' + QUOTENAME(CU.COLUMN_NAME) + ' as nvarchar(max))' 
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
 INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU ON TC.TABLE_NAME = CU.TABLE_NAME
 AND TC.TABLE_SCHEMA = CU.TABLE_SCHEMA 
 AND Tc.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
 WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY' AND TC.TABLE_SCHEMA = @Table_Schema AND TC.TABLE_NAME = @Table_Name 
 ORDER BY CU.COLUMN_NAME
 FOR XML PATH('')),1,9,'') 

 IF @PkColumn IS NULL
    SELECT @PkColumn = 'cast(NULL as nvarchar(max))' 
    
 -- set select statement using dynamic UNPIVOT
 DECLARE @SQL NVARCHAR(MAX)
 SET @SQL = 'select *, ' + QUOTENAME(@Table_Schema,'''') + 'as [Table Schema], ' + QUOTENAME(@Table_Name,'''') + ' as [Table Name]' +
  ' from 
  (select '+ @PkColumn + ' as [PK Column], ' + @Cols + ' FROM ' + QUOTENAME(@Table_Schema) + '.' + QUOTENAME(@Table_Name) +  ' )src UNPIVOT ([Column Value] for [Column Name] IN (' + @Columns + ')) unpvt 
 WHERE [Column Value] LIKE ''%'' + @SearchString + ''%'''
 
 --print @SQL

EXECUTE sp_ExecuteSQL @SQL, N'@SearchString nvarchar(max)', @SearchString 
END
GO

IF OBJECT_ID('TempDB..#Result', N'U') IS NOT NULL DROP TABLE #Result;
CREATE TABLE #RESULT ([PK COLUMN] NVARCHAR(MAX), [COLUMN VALUE] NVARCHAR(MAX), [COLUMN Name] sysname, [TABLE SCHEMA] sysname, [TABLE Name] sysname)
DECLARE @Table_Name sysname, @SearchString NVARCHAR(MAX), @Table_Schema sysname
SET @SearchString = N'Cost'

DECLARE curAllTables CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR
    SELECT   Table_Schema, Table_Name
    FROM     INFORMATION_SCHEMA.Tables    
    WHERE TABLE_TYPE = 'BASE TABLE'
    ORDER BY Table_Schema, Table_Name
    
    OPEN curAllTables
    FETCH  curAllTables
    INTO @Table_Schema, @Table_Name    
	WHILE (@@FETCH_STATUS = 0) -- Loop through all tables in the database
      BEGIN
		INSERT #RESULT 
		EXECUTE spSearchStringInTable @SearchString, @Table_Schema, @Table_Name
    
        FETCH  curAllTables
        INTO @Table_Schema, @Table_Name
      END -- while
    CLOSE curAllTables
    DEALLOCATE curAllTables
  -- Return results 
  SELECT * FROM #RESULT ORDER BY [Table Name]

It works OK for me.

I also posted another code today which searches a single column in all tables of the database. I spent about an hour working on that code and it works fine for me too.

Marked as answer by
Ed Price — MSFTMicrosoft employee
Tuesday, April 29, 2014 7:54 AM

if that’s the case, then:

declare @schema varchar(100), @table varchar(100), @column varchar(100), @dSQL nvarchar(MAX), @result varchar(100)
declare @results table (sname varchar(100), tname varchar(100), cname varchar(100), result varchar(100))

declare c1 cursor for 
select s.name, t.name, c.name, 
'select @res = ['+c.name+'] from '+s.name+'.'+t.name+' where convert(varchar,['+c.name+']) = ''thevalue'''
  from sys.tables t
    inner join sys.schemas s
      on t.schema_id = s.schema_id
    inner join sys.columns c
      on t.object_id = c.object_id 
 where c.name like 'thecolumname' 
 open c1
 fetch next from c1 into @schema, @table, @column, @dSQL
 while @@FETCH_STATUS <> -1 
 begin
 set@result=NULL
 EXECUTE sp_executesql @dSQL, N'@res varchar(100) OUTPUT', @res = @result OUTPUT
insert into @results (sname, tname, cname, result)
  values (@schema, @table, @column, @result)
  fetch next from c1 into @schema, @table, @column, @dSQL
 end
 close c1
 deallocate c1

 select * from @results

Edited by
Patrick Hurst
Thursday, April 24, 2014 7:27 PM
Marked as answer by
Khurj01
Thursday, April 24, 2014 7:31 PM

If you need to search all tables in a specific column, what is the desired result?

In this case we want to create a separate procedure (spSearchColumnNameAllTables). I created it and tested. Sounds like a good thing for a new article, BTW:

IF NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.ROUTINES
		WHERE ROUTINE_NAME = 'spSearchAllTablesColumnName'
			AND ROUTINE_TYPE = 'PROCEDURE'
		)
	EXECUTE ('CREATE PROCEDURE dbo.spSearchAllTablesColumnName AS SET NOCOUNT ON;');
GO

ALTER PROCEDURE spSearchAllTablesColumnName (
	@ColumnName SYSNAME
	,@SearchString NVARCHAR(max)
	)
AS
DECLARE @Tables TABLE (
	TableName SYSNAME
	,TableSchema SYSNAME
	,PKColumn NVARCHAR(max)
	)

INSERT INTO @Tables (
	TableName
	,TableSchema
	,PKColumn
	)
SELECT Table_name
	,table_schema
	,COALESCE(STUFF((
				SELECT N' + ''|'' + ' + ' cast(' + QUOTENAME(CU.COLUMN_NAME) + ' as nvarchar(max))'
				FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
				INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU ON TC.TABLE_NAME = CU.TABLE_NAME
					AND TC.TABLE_SCHEMA = CU.TABLE_SCHEMA
					AND Tc.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
				WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
					AND TC.TABLE_SCHEMA = C.TABLE_SCHEMA
					AND TC.TABLE_NAME = C.TABLE_NAME
				ORDER BY CU.COLUMN_NAME
				FOR XML PATH('')
				), 1, 9, ''), 'cast(NULL as nvarchar(max))')
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE COLUMN_NAME = @ColumnName
	AND DATA_TYPE IN (
		'text'
		,'ntext'
		,'varchar'
		,'nvarchar'
		,'char'
		,'nchar'
		);

IF @@ROWCOUNT = 0
BEGIN
	DECLARE @dbName SYSNAME;

	SET @dbName = QUOTENAME(DB_NAME(),'''');

	RAISERROR (
			'No tables have %s column in the database %s'
			,16
			,1
			,@ColumnName
			,@dbName
			);

	RETURN - 1;
END

DECLARE @SQL NVARCHAR(max);

SELECT @SQL = STUFF((
			SELECT 'UNION ALL 
SELECT ' + PKColumn + ' AS PK, ' + quotename(TableSchema, '''') + ' AS TableSchema, ' + quotename(TableName, '''') + 
' AS TableName, ' + quotename(@ColumnName) + ' AS ' + 
 quotename(@ColumnName) + ' FROM ' + quotename(TableSchema) + '.' + quotename(TableName) + 
 ' WHERE ' + quotename(@ColumnName) + ' LIKE ''%' + @SearchString + '%'''
			FROM @Tables
			ORDER BY TableSchema
				,TableName
			FOR XML PATH('')
				,type
			).value('.', 'nvarchar(max)'), 1, 10, '')

PRINT @SQL

EXECUTE sp_executeSQL @SQL
	,N'@SearchString nvarchar(max)'
	,@SearchString
GO

EXECUTE spSearchAllTablesColumnName 'descrip'
	,'test';

Edited by
Naomi N
Thursday, April 24, 2014 5:03 PM
Marked as answer by
Ed Price — MSFTMicrosoft employee
Tuesday, April 29, 2014 7:54 AM

Источник

Rank

Looker Staff
0 replies

Last tested: Feb 2021

This error message can appear when a table no longer exists but still shows in the list of tables for this schema.

Error Message

Error Running SQL ERROR: relation "schema.table" does not exist

Troubleshooting

Go to the gear option next to the connection name and hit «Refresh Tables and Schema» if the table has been renamed/deleted then it will no longer show up under the reference name that is causing the error.

This can also happen if the table is defined with mixed-case spelling and you try to query it without double-quotes. See this stackoverflow post

In other words, if hotelList was created with mixed-case spelling, this won’t work:

play_arrow

SELECT *
FROM hotellist
LIMIT 10

Because the tablename is converted to lowercase when the query is run, this also won’t work:

play_arrow

SELECT *
FROM hotelList
LIMIT 10

To avoid this issue, use double-quotes to use the specific mixed-case spelling as the table is defined. This will work:

play_arrow

SELECT *
FROM "hotelList"
LIMIT 10

It is also important to check for fields that reference a field from a joined view WITHOUT ${} syntax, in this case the error surfaces because Looker does not know where to look for the referenced field.

For Snowflake, specifically, ensure that the table names are all UPPERCASE in the database. As per this doc, unquoted values are defaulted to uppercase in Snowflake. That’s why we’ll usually see the error:
SQL compilation error: Object 'DATABASE.SCHEMA.TABLE_NAME' does not exist when we run
SELECT Procfile README.md all_cards.json body_after.txt body_before.txt body_during.txt ids.txt jq main.sh tmp FROM schema.table_name

If we run:
SELECT Procfile README.md all_cards.json body_after.txt body_before.txt body_during.txt ids.txt jq main.sh tmp FROM "schema"."table_name"
we should see the query run successfully because the lower case will be preserved.

Источник

During a backup (using PGADMIN III or pg_dump) the following error occurs:

schema with OID xxxxxx does not exist

Perform a backup of one database using PGADMIN or pg_dump
The «schema with OID xxxxxx does not exist» is raised and stop the Backup

The error is due to a corrupted database for example because of a network failure while inserting data in Cast Storage Service or when dropping a schema.

You need to delete rows on catalog tables where specified OID is specified.
```
 select oid, nspname from pg_namespace where oid=xxxxxxx;
```
The catalog pg_namespace stores namespaces. A namespace is the structure underlying SQL schemas.

Status of the query

If there is no rows raised, that means that the schema has been dropped. In that case, go to the point 2.

If there is one row raised, then a solution is to perform a component reinstall. You don’t need to go to the point 2.
If there are no entries when the above query is run, it means that this namespace has been removed from the Server. Then we assume that some entries in the following system catalog are corrupted. You have to remove the rows that are raised in the following queries

1. ```
 select * from pg_type where typnamespace=xxxxxxx; 
```
  The catalog pg_type stores information about data types. Base types and enum types (scalar types) are created with CREATE TYPE, and domains with CREATE DOMAIN
2. ```
 select * from pg_class where  relnamespace = xxxxxxx; 
```
  The catalog pg_class catalogs tables and most everything else that has columns or is otherwise similar to a table. This includes indexes (but see also pg_index), sequences, views, composite types, and TOAST tables
3. ```
 select * from pg_operator where   oprnamespace = xxxxxxx; 
```
  The catalog pg_operator stores information about operators.
4. ```
 select * from pg_conversion where connamespace = xxxxxxx; 
```
  The catalog pg_conversion describes the available encoding conversion procedures
5. ```
 select * from pg_opclass where opcnamespace = xxxxxxx; 
```
  The catalog pg_opclass defines index access method operator classes.
6. ```
 select * from pg_aggregate where aggfnoid = xxxxxxx or aggtransfn  = xxxxxxx or aggfinalfn = xxxxxxx; 
```
  The catalog pg_aggregate stores information about aggregate functions. An aggregate function is a function that operates on a set of values (typically one column from each row that matches a query condition) and returns a single value computed from all these values. Typical aggregate functions are sum, count, and max.
7. ```
 select * from pg_proc where pronamespace = xxxxxxx;
```
  The catalog pg_proc stores information about functions (or procedures).

—> Delete rows where oid xxxxxx appears and re-execute the backup in PGADMIN or pg_dump, it should work.

Источник

Question

Answers

Question

Answers

Error Message

Troubleshooting