Double Dot Table Qualifier

Double Dot table qualifier

Thanks to this dot, the default schema (dbo) is choosen for your query.

When you have two databases it is required to give the full path to the table.
If we have:
Database1 schema: dbo, guest table dbo.A, guest: A
Database2 schema: dbo, guest table dbo.B, guest: B

if we create select statement like:

select * from Database2..B

We are selecting data from dbo.B table
IF we would like to specify schema we need to refer as

select * from Database2.schemaname.tablename

EDIT:
As colleagues pointed out, the default schema can be changed in database, however in this particular example it seems to be dbo :)

oracle sql - using double ampersand (&&) and double dot (..)

@Gary_W has covered the difference between a single and a double ampersand.

The double period is perhaps slightly less obvious. From the SQL*Plus documentation:

If you wish to append characters immediately after a substitution variable, use a period to separate the variable from the character.

If you had a single period:

&&DATABASE_ONE.TABLE_ONE

then that period would be treated as the terminator for the substituion variable name, and would be consumed in the process. Say the value you entered was 'HR'; the substitution would be:

old:select &&DATABASE_ONE.TABLE_ONE from dual
new:select HRTABLE_ONE from dual

select HRTABLE_ONE from dual

As you can see, there is now no period between the schema and table names, giving you a combined identifier which will not represent what you intended.

With the form you have:

&&DATABASE_ONE..TABLE_ONE

the second period is the 'normal' one that sits between those two elements; once the first has been consumed by the substitution, the second remains to fulfil that function:

old:select &&DATABASE_ONE..TABLE_ONE from dual
new:select HR.TABLE_ONE from dual

select HR.TABLE_ONE from dual

SQL: What does the .. represent in common..TABLE_NAME

common..DELIVERY_TABLE s

The complete four parts to locate a object is SERVER.DATABASE.SCHEMA.OBJECTNAME

SERVER could be omitted if you are in the current server,

Database could be omitted if you are in the current databse,

Schema could be omitted if you know your default schema name is DBO

so for the above: is just common(database name).DBO(schema name).DELIVERY_TABLE,

for the last s is just the alias your gave to your table for the later join, but I would recommend using as s for easier read

SQL: Pull List Of Tables From Specified Database While Attached To Another

Okay, after spending all day working on this, I have finally come up with a solution. I load all the databases into a table variable, then I begin looping through those databases and send back their details to the client. After the database details themselves have been sent to the client via RAISERROR I then utilize sp_executesql to execute a new sub-query with the current database specified to get the list of tables for processing at the end of the primary. The example below demonstrates the basic structure of this process for others experiencing this issue in the future.

Thank you all once again for your help!

-Jamie

DECLARE @LoopCounter INT = 1, @DatabaseCount INT = 0;
DECLARE @SQL NVARCHAR(MAX), @dbName NVARCHAR(MAX);
DECLARE @Databases TABLE ( _id INT, _name NVARCHAR(MAX) );
DECLARE @Tables TABLE ( _name NVARCHAR(MAX), _type NVARCHAR(15) );

INSERT INTO @Databases
SELECT ROW_NUMBER() OVER(ORDER BY name) AS id, name
FROM sys.databases
WHERE name NOT IN ( 'master', 'tempdb', 'msdb', 'model' );

SET @DatabaseCount = (SELECT COUNT(*) FROM @Databases);

WHILE (@LoopCounter <= @DatabaseCount) BEGIN
    SET @dbName = (SELECT _name FROM @Databases WHERE _id = @LoopCounter);
    SET @SQL NVARCHAR(MAX) = 'SELECT TABLE_NAME, TABLE_TYPE
                          FROM [' + @dbName + '].INFORMATION_SCHEMA.TABLES';
        INSERT INTO @Tables EXEC sp_executesql @SQL;
    SET @LoopCounter += 1;
END

what does select * from a..c means

the fisrt part (a) is the Database name and the (..) indicates the default schema (dbo) and c is the table name so:-

    select * from a..c 

is equal to

    select * from a.dbo.c 

Is using LIKE '%%x%%' different than using LIKE '%x%'?

There is literaly no difference between:

SELECT * FROM dbo.names WHERE name LIKE '%%st%%ack%%flow%%'

and

SELECT * FROM dbo.names WHERE name LIKE '%st%ack%flow%'

% wildcard allows any or no characters.

---

The only difference is when % is escaped:

CREATE TABLE names(name VARCHAR(10));
INSERT INTO names(name) VALUES('%b');
INSERT INTO names(name) VALUES('%ab');

SELECT * FROM names WHERE name LIKE '%%b';
SELECT * FROM names WHERE name LIKE '%%b' ESCAPE '%';

db<>fiddle demo

---

Related Topics