SQL Practice 1 - ResultSets , Object ID

SELECT OBJECT_ID('[ErrorLog]')
SELECT OBJECT_name(277576027)
SELECT OBJECT_NAME(object_id), SCHEMA_NAME(schema_id), * FROM sys.tables


sys_objects
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-objects-transact-sql?view=sql-server-ver15


all types in SQL

SQL Lessons sys.tables, objectIID

sys.tables

sys.tables is a system table and is used for maintaining information on tables in a database. For every table added to the database, a record is created in the sys.tables table. There is only one record for each table and it contains information such as table name, object id of table, created date, modified date, etc. Object ID is unique


Select name AS TableName, object_id AS ObjectID
From sys.tables
From sys.tables
–– where name = '<TABLENAME>'
–– Uncomment above line and add <Table Name> to fetch details for particular table



sys.columns
sys.columns is a system table and is used for maintaining information on columns in a database. For every column added in a database, a record is created in the sys.columns table. There is only one record for each column and it contains the following information:
Name: The name of the column. This is unique within the table object.
Object_id: object_id is unique identifier for the table in which the column exists. We will use this column to join sys.columns with sys.tables in order to fetch columns in different tables.
Column_id: ID of the column. This is unique within the table object./li>
user_type_id: System code for column data type
max_length: Maximum length (in bytes) of the column.
is_nullable: 1 if column is nullable.





Add Data Type Name

The next step is to replace Data Type ID with Data Type Name. This can be done by joining the above query with sys.types table.
About sys.types

sys.types is a system table and is used for maintaining information on column data types in a database. This table contains one row for each data type and includes the following information:
Name: The name of the column. This is unique within the table object.
user_type_id: System code for column data type. This is unique for this table and is used for joining with sys.columns table.
max_length: Maximum length (in bytes) of the column.


reference:
https://docs.microsoft.com/en-us/sql/t-sql/functions/object-id-transact-sql?view=sql-server-ver15&viewFallbackFrom=sql-server-2014



Null Validation:
USE AdventureWorks2012; GO IF OBJECT_ID (N'dbo.AWBuildVersion', N'U') IS NOT NULL DROP TABLE dbo.AWBuildVersion; GO

GAC Location

to open GAC from windows run folder, type in run command

C:\windows\assembly\GAC_MSIL

to open from explorer
C:\windows\assembly

CTE with full outer join example

With CountryCTE(CountryName,Export)
As (SELECT C.Country,ISNULL(sum(O.sumorders),0) as export
FROM [companies] C
full outer JOIN
(
    SELECT seller,sum(value) AS sumorders
    FROM  [dbo].[trades]
    GROUP BY seller   
)O ON C.[name] = O.[seller]
GROUP BY C.Country),
 MyCountryCTE(CountryName,Import)
 As
 (SELECT C.Country,ISNULL(sum(O.sumorders),0) as import
FROM [companies] C
full outer JOIN
(
    SELECT buyer,sum(value) AS sumorders
    FROM  [dbo].[trades]
    GROUP BY buyer   
)O ON C.[name] = O.[buyer]
GROUP BY C.Country)
select CountryCTE.CountryName,Export,Import from CountryCTE full outer join MyCountryCTE on CountryCTE.CountryName=MyCountryCTE.CountryName
order by CountryCTE.CountryName