Schema a does not exist в чем ошибка - ErrorsMaster.ru - большая энциклопедия ошибок и их решений

How can i get this query to work?

SELECT weather.id, cities.name, weather.date, weather.degree
FROM weather JOIN weather.city_id ON cities.id
WHERE weather.date = '2011-04-30';

ERROR: schema «weather» does not exist.

weather is not schema, it’s a table!

asked Apr 29, 2011 at 8:36

perhaps:

SELECT weather.id, cities.name, weather.date, weather.degree 
FROM weather JOIN cities ON (weather.city_id = cities.id)
WHERE weather.date = '2011-04-30';

postgres is complaining about the join on weather.city_id which is interpreted as a table/view called ‘city_id’ in schema ‘weather’

answered Apr 29, 2011 at 8:51

Источник

I managed to create a table in a schema FOO.

Whenever I then try and do anything, like even a basic select, I just get:

ERROR: schema "FOO" does not exist
SQL state: 3F000
Character: 15

I am running the select in the same edit window as I created (using pgAdmin4). I get the same error when I try and create a view call FOO.Info. Yet when I try and create a new table in FOO it works.

What’s going on? I am using the same syntax to refer to the table in the select as the create.

# worked fine
CREATE TABLE "FOO"."Events"
(
...

# all these have the error
select * from "FOO"."Events";
select * from FOO.Events;
select * from Foo.Events;

postgres=# dn
  List of schemas
  Name  |  Owner   
--------+----------
 foo    | postgres
 public | postgres
(2 rows)

asked May 19, 2017 at 7:16

I believe you created it as

create schema FOO;

which creates schema «foo», not «FOO»

And then you reference it as

select * from "FOO".table_name

so it is not found

answered May 19, 2017 at 7:18

Vao TsunVao Tsun

46.6k12 gold badges98 silver badges131 bronze badges

Источник

Go to SQL

Error : Schema ‘XYZ’ doesnt not exist — Redshift

Hey guys,

Currently I am writing this query for a project. The structure is as follows:

With ABC as (Select ...................... from ............. join .......... group by ..............)

, XYZ as (Select ...................... from ............. join .......... group by ..............)

Select .....................

from XYZ

Join ABC

ON ......=...... and ......=......

where ..........

group by ............

After Executing I get the error schema XYZ does not exist.

Can anyone enlighten under what circumstances can I get this error where I have already defined the temporary table XYZ as shown above. I dont think there is any syntax error but still if I am missing some rules please enlighten.

Also, I was thinking of using CREATE TEMP TABLE AS instead of WITH AS. Any suggestions on this? I tried but having syntax errors like ‘Error near CREATE’ . Tried to search online but nowhere they have examples of creating 2 temp tables and syntax used in that.

Thanks in advance for helping me learn.

Источник

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

Источник

How can i get this query to work?

SELECT weather.id, cities.name, weather.date, weather.degree
FROM weather JOIN weather.city_id ON cities.id
WHERE weather.date = '2011-04-30';

ERROR: schema «weather» does not exist.

weather is not schema, it’s a table!

asked Apr 29, 2011 at 8:36

perhaps:

SELECT weather.id, cities.name, weather.date, weather.degree 
FROM weather JOIN cities ON (weather.city_id = cities.id)
WHERE weather.date = '2011-04-30';

postgres is complaining about the join on weather.city_id which is interpreted as a table/view called ‘city_id’ in schema ‘weather’

answered Apr 29, 2011 at 8:51

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

I receive a «schema does not exist» error when trying to load a raster to a PostGIS database (see full error at the bottom). The schema does exist in the database. This is the syntax I’m using:

raster2pgsql -s 2048 -I -C -Y C:UsersMy_BookDesktopLOAD2POSTGISAspect.tif environmental.aspect > Aspect.sql
psql -h localhost -U postgres -d Howbill -f Aspect.sql

I am able to load the raster into the default public schema if I leave out environmental.aspect. However, that leads to other problems when moving the raster table from the public schema to another schema. More details regarding the moving from public schema to other schemas can be viewed here:

Can’t view PostGIS rasters after changing schemas

**Full error:**

C:Program Files (x86)PostgreSQL9.2bin>raster2pgsql -s 2048 -I -C -Y C:UsersMy_BookDesktopLOAD2POSTGISAspect.tif environmental.aspect > Aspect.sql
psql -h localhost -U postgres -d Howbill -f Aspect.sql
Processing 1/1: C:UsersMy_BookLOAD2POSTGISAspect.tif

C:Program Files (x86)PostgreSQL9.2bin>psql -h localhost -U postgres -d Howbi
ll -f Aspect.sql
BEGIN
psql:Aspect.sql:2: ERROR:  schema "environmental" does not exist
psql:Aspect.sql:3: ERROR:  current transaction is aborted, commands ignored unti
l end of transaction block
psql:Aspect.sql:5: invalid command .
psql:Aspect.sql:6: ERROR:  syntax error at or near "010000010013"
LINE 1: 010000010013B444D4496B144013B444D4496B14C082F38736BB1CD94011...
        ^
psql:Aspect.sql:7: ERROR:  current transaction is aborted, commands ignored unti
l end of transaction block
ROLLBACK

C:Program Files (x86)PostgreSQL9.2bin>

Thanks @dimitri for the feedback! This makes sense now:

So I guess that in your case the schema in the logs and the MySQL database in the connection string are the same thing?

That is correct. I’ve removed the specific names from the example, but my intention is for the MySQL database name to be loaded into a PostgreSQL database by the same name, but in the public schema.

pgloader was attempting to create a table in the schema («schema» — obfuscated in the example) before this schema existed. Your instruction to run ALTER SCHEMA 'schema' RENAME TO 'public' after the data is migrated would rename the schema from «schema» to «public». Had I done this, it would remove the need to alter the user search path as «public» is included by default.

Older versions of pgloader must default to the «public» schema? I neglected to mention that the database existed already in PostgreSQL, and with it the default «public» schema. But any other schemas would also need to be created prior, or with the --with create-schemas flag on pgloader.

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

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.

Источник

Question

Answers

Question

Answers

Question

Answers

Question

Answers

Question

Answers

Error Message

Troubleshooting