Difference between INFORMATION_SCHEMA and SYS Objects in SQL Server

1.    INFORMATION_SCHEMA is an ANSI standard set of READ-ONLY views which provide information about all of the tables, views, columns, and procedures in a database. All RDBMS have to provide this views and our query would work in all RDBMS (oracle, sql, mysql etc.) On the other hand SYS schema is specific to SQL product and would have much more details.

2.    SYS objects would be having more information related to SQL Objects rather than INFORMATION_SCHEMA.

The INFORMATION_SCHEMA is part of the SQL-92 standard, so we can use it in other database engines in future stage. In SQL Server, these views exist in each database. It is not under the "dbo" user. Instead they are under the ownership of INFORMATION_SCHEMA. Therefore, to access any of the views, we need to preface the view name with "INFORMATION_SCHEMA which is like below.

SELECT * FROM INFORMATION_SCHEMA.TABLES

Each of the views in this collection exposes a different aspect of the meta data of the database.

Learning Salesforce – Part-1

Application in salesforce

An application in Salesforce.com is a container, which contains - a name, a logo, and an ordered set of tabs and objects. Some standard applications are there in our organization like Sales, Marketing Etc.

Path for Creating Custom application:

SetupàBuildàCreateàApps.

Objects in salesfoce

API objects represent database tables that contain your organization's information.  The term “record” describes a particular occurrence of an object .A record is analogous to a row in a database table. A field is analogous to Column in a database table.

Some Objects already created for you by Sales force those are called standard objects. Objects you create in your organization are called custom objects.

Path to create Custom objects

Setupà BuildàCreateàobjectsàNew custom object.

By using the above path under an application we can create custom objects.

While creating objects we have to allow that object information to create reports and activities. After creation of object the page you will get is called as object definition page. Whenever we are creating an object by default some standard fields will created those are object name, Last modified by, Owner, Created by. If we want to edit standard fields we can edit, but we cannot be able to delete those fields.


Path for Creating Fields in Custom objects

SetupàCreate àObjects-à(select the object) Custom fields & Relationships.

Path for Creating Fields in Standard objects

Setupà BuildàCustomize à(Select the object) FieldsàCustom fields &Relationships.

Page layouts

It controls the layout and organization of detail and edits pages. It manages selection of fields, related lists, and custom links users on detail and edits pages. It also manages the standard and custom buttons display on detail pages and related lists. It determines whether fields are visible, read only, or required, on detail and edit pages only.

Path to create Page layout for standard objects

SetupàBuildàCustomize à(Select the object)PagelayoutàNew pagelayout


Path to create Page layout for custom objects 

SetupàBuildàCreateàObjectsà(Select object)PagelayoutàNew pagelayout

Page layout Assignment

Page layout assignment is to assign or to see for each and every profile what is the layout. Path is same as pagelayout beside “new page layout” there should be a button called “Page layout assignment”.

Record types:

Record types allow you to offer different business processes, picklist values, and page layouts to different users. Record types can be used in various ways, i.e, If we are having different business process and we want to save those information in a single object at that time we will go for record types.

Path to setup Record Type on Standard Objects:

SetupàBuildàCustomizeà(Select object) record TypeàNew record type


Path to setup Record Type on Custom Objects:  

SetupàBuildàCreateàObjectsà(Select object)Record typesàNew record type
For each record types we can assign a different page layout. We can assign it while creating the record type or after creating record types we have to go for layout assignment.

Page layout assignment for Record types

In object definition page in record types section “Page layout Assignment” would be there beside “new” . There we can choose a unique page layout for each Record type.

Script to replace a String in entire programming script in SQL Server

During server\database migration, we may need to replace a specific string in all the programming script like stored procedures, Triggers and Views. It is really very difficult to go and replace in each procedure separately. Below script will produce the source code by replacing the specific string in all the programming script. We can simply copy and execute it in query analyzer. This makes life easier.

The following script will replace “TestSystems” by “PRODSystems”.

Script

SELECT 'GO

            ' +  REPLACE(REPLACE(REPLACE(REPLACE([definition], '[TestSystems]', '[PRODSystems]'), 

            'CREATE PROCEDURE', 'ALTER PROCEDURE'),

            'CREATE TRIGGER', 'ALTER TRIGGER'),

            'CREATE VIEW','ALTER VIEW')

 FROM

                        SYS.SQL_MODULES

 WHERE

                        [definition] LIKE '%[TestSystems]%';

Script to find the Restored Database DATETIME

Below script is used to find the restored databases details including specific restored DATETIME.

Script

WITH  CTE_RESTORE_DATETIME

 AS

 (

SELECT  

                                     SD.name AS DatabaseName

                                    , RH.restore_date AS BackUpRestoredDatetime

                                    , ISNULL(RH.user_name, 'Restore Not Happened') AS RestoredBy

                                    , BS.name AS BackUpName

                                    , BS.user_name AS BackupCreatedBy

                                    , BS.backup_finish_date AS backupCompletedDatetime

                                    , BS.database_name AS BackupSourceDB

                                    , BS.server_name AS BackupSourceSQLInstance

                                    , ROW_NUMBER() OVER ( PARTITION BY SD.name ORDER BY RH.restore_date DESC ) AS RestoreOrder

FROM    

                                    SYS.DATABASES AS SD

                                    LEFT JOIN MSDB.DBO.RESTOREHISTORY AS RH ON SD.name = RH.destination_database_name

                                    LEFT JOIN MSDB.DBO.BACKUPSET AS BS ON RH.backup_set_id = BS.backup_set_id

  )

SELECT 

                                      CRD.DatabaseName

                                    , CRD.BackUpRestoredDatetime

                                    , CRD.RestoredBy

                                    , CRD.BackUpName

                                    , CRD.BackupCreatedBy

                                    , CRD.backupCompletedDatetime

                                    , CRD.BackupSourceDB

                                    , CRD.BackupSourceSQLInstance

                                    , CRD.RestoreOrder

FROM   

                                    CTE_RESTORE_DATETIME AS CRD

WHERE  

                                    RestoreOrder = 1

ORDER BY

                                    CRD.DatabaseName

Script to monitor tempdb usage in SQL Server

Use the below query to monitor the tempdb usage in SQL Server.

The following query returns the total number of free pages and total free space in megabytes (MB) available in all files in tempdb.

SELECT

            SUM(unallocated_extent_page_count) AS [free pages],

            (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]

FROM

            sys.dm_db_file_space_usage

Script to get all Table sizes in SQL Server

Monitoring individual table’s growth is essential operation for DBA as well as application developer\support members. This will be helpful to take certain decision like to go for Archival process, table partitioning, backup and implementing high availability solution etc.,

Below script will automate this process. We just need to define threshold value and schedule it through SQL Agent or task scheduler. It also helps to pro-actively preventing unavoidable situations for Database and Applications.

CREATE PROCEDURE [USP_FETCH_ALL_TABLE_Size]

AS

DECLARE @TableName VARCHAR(100)

DECLARE CUR_Sysobjects CURSOR

FOR

SELECT

                        [name]

FROM

                        sysobjects

WHERE 

                        OBJECTPROPERTY(id, N'IsUserTable') = 1

FOR READ ONLY

DECLARE @Tab_Var AS TABLE

(

    TableName                                  VARCHAR(100),

    NumberOfRows            INT,

    ReservedSize                  VARCHAR(50),

    DataSize                                       VARCHAR(50),

    IndexSize                                     VARCHAR(50),

    UnusedSize                                 VARCHAR(50)

)

OPEN CUR_Sysobjects

--Get the first table name from the cursor

FETCH NEXT FROM CUR_Sysobjects INTO @TableName

--Loop until the cursor was not able to fetch

WHILE (@@Fetch_Status >= 0)

BEGIN

    --Dump the results of the sp_spaceused query to the temp table

    INSERT  @Tab_Var

        EXEC sp_spaceused @TableName

    --Get the next table name

    FETCH NEXT FROM CUR_Sysobjects INTO @TableName

END

CLOSE CUR_Sysobjects

DEALLOCATE CUR_Sysobjects

--Select all records so we can use the reults

SELECT 

                        TableName AS TableName,

                        NumberOfRows AS NumberOfRows,

                        CAST ((CONVERT(NUMERIC, substring(dataSize,1, charindex('KB', dataSize)-1))/1024/1024) AS DECIMAL(6,2)) As 'DataSize IN GB'           

FROM

                        @Tab_Var

Order BY

                        NumberOfRows DESC

--EXEC [USP_FETCH_ALL_TABLE_Size]

Standard Objects in Salesforce

Each of the tabs in Salesforce dashboard called as Objects which represents major module or data element in an interconnected database. Each object is defined with set of fields.

Leads are defined as business deals. It maintains all details with respect to customer requests which include customer’s company details, customer details and the specific business request. Once lead is converted to opportunity, respective data will be moved to Accounts, contacts and opportunities module accordingly.

Accounts are companies that we do business with. We can track all types of accounts, including customers, prospects, partners, and competitors.

Contacts are individuals associated with accounts.

Opportunities are the deals that we pursue to drive revenue for our company.

Cases are customer inquiries that our support teams work to manage and resolve.

Solutions are answers to cases and other frequently asked questions.

Documents are the sales and marketing collateral and documents that we use as part of our selling or service processes.

Reports are data analyses for us and our entire organization. Salesforce provides a variety of best practices reports, and we can build custom reports on the fly to better measure our business.

Dashboards are graphs, charts, and tables based on our custom reports. We can use dashboards to visually measure and analyze key elements of our business.

Products are our company’s products and services, associated with the prices for which we offer them. We can link products and their prices to our opportunities.

Forecasts are our best estimates of how much revenue or product we can close in a fiscal period, depending on the way we forecast.

Campaigns are specific marketing activities that we manage to drive leads, build a brand, or stimulate demand.

Contracts are the agreements involved in our selling or support process. We can use contracts in Salesforce to manage our approval and renewal processes and keep important documents in one place.