By: John Miner | Updated: 2015-05-26 | Comments | Related: > Azure
Problem
Microsoft released the preview version of Azure SQL Database V12 in December of 2014.
The main purpose of this version is to narrow the syntactical differences between the on-premises and in-cloud database engines. The hope is that more companies will migrate their data to
this software as a service platform.
The diversity of database types and the locations of these databases continues to grow for the data professional. It is very difficult to be an expert at all of them.
We can use a OLE driver to make a
linked server connection to these new data marts.
This allows us to perform CRUD (Create, Read, Update, Delete) statements against the tables or execute stored procedures if given the right permissions.
As a database administrator, one might wonder "How can I understand the schema and security of these new data marts?".
Solution
Microsoft has supplied system stored procedures for the database developer to query the
Linked Servers for this type of meta data. Since most of these stored procedures return tables as output, I took the liberty of creating table variables
to store the output of each call.
To refresh your memory, I included the following MSDN diagram which shows the basic configuration of a linked server. It is interesting to note that Oracle, MS Access and MS SQL Server
are listed as the data sources.
Business Problem
I can image in the future that a small publishing company might buy an on-line ordering system which was built with Microsoft Azure Technologies. Since the company has been in business
for many years, they might have a on premises ERP system that runs on top of a SQL Server database. Your manager wants you to report back with a better understanding of the data mart.
The article on
Azure Linked Servers is your prerequisite for this demonstration. I am assuming you created a linked server, named MyAzureDb,
on a local server which points to a Azure SQL database named pubs in a Microsoft data center in the cloud.
Knowing the data source
Because this is a tips website for Microsoft SQL Server, lets start with something we know. The database engine comes with a bunch of
catalog views that can be used to research the schema and security of a database.
The code below looks at all the objects in the pubs database and returns the top 3 objects of a given type ordered by name for the ranking.
Notice I am using a 4 part notation to reference the linked server name, database name, schema name and view name.
The sys.objects view is used to return
information about every object in the database. This example not only shows that the catalog views are available in Azure SQL Database, but advanced windowing
functions work just fine.
/*
Show top 3 objects by type ranked by name
*/
SELECT * FROM
(
SELECT
[NAME], [TYPE], [TYPE_DESC],
ROW_NUMBER() OVER (PARTITION BY [TYPE] ORDER BY [NAME]) AS ROWID
FROM MYAZUREDB.PUBS.SYS.OBJECTS
WHERE IS_MS_SHIPPED = 0
) AS T
WHERE T.ROWID < 4
GO
Output from executing the code.
Catalog Information
The power of linked servers is the ability to work with heterogeneous data sources via a single SQL Server instance. The catalog views will not help us when the provided linked server talks to an Microsoft Access database.
Therefore, the system stored procedures for linked servers are very important.
Every code example from now on will follow this simple algorithm.
- Create a table variable to catch output.
- Insert into table variable by calling system stored procedure.
- Examine data in the table variable.
The first step in any data discovery is to list the databases on the server. The @LsCatalogs table variable catches the output from executing the sp_catalogs stored procedure. Selecting from the @LsCatalogs table variable shows the two databases in MyAzureDb.
/*
Show local linked servers
*/
-- Variable to hold catalog info
DECLARE @LsCatalogs TABLE
(
CATALOG_NM sysname NULL,
DESCRIPTION_TXT varchar(4000) NULL
);
-- Grab databases
INSERT INTO @LsCatalogs
EXEC sp_catalogs 'MyAzureDb';
-- Show the information
SELECT * FROM @LsCatalogs
GO
Output from executing the code.
Table/View Information
The basic building block for any relational database is a table. A view is just a compiled SQL statement that references the underlying tables. If we really want to understand a new data mart, we will need to get a list of tables and views.
The code below calls the sp_tables_ex stored procedure to return a list of catalogs (databases), schemas, and tables.
/*
Show tables n views
*/
-- Variable to hold view/table info
DECLARE @LsTables TABLE
(
TABLE_CAT sysname NULL,
TABLE_SCHEM sysname NULL,
TABLE_NAME sysname NULL,
TABLE_TYPE varchar(32) NULL,
REMARKS varchar(254) NULL
);
-- Grab views/tables
INSERT INTO @LsTables
EXEC sp_tables_ex MyAzureDb;
-- Show the information
SELECT * FROM @LsTables
WHERE TABLE_TYPE NOT LIKE 'SYSTEM%'
GO
Output from executing the code.
Please note that not all OLE DB providers support every system stored procedure.
For instance, Microsoft Excel using the Microsoft.ACE.OLEDB.12.0 driver does not support sp_tables_ex. However, you can execute CRUD statements against an Excel Linked Server just fine.
Table Privileges Detailed
Once we know the tables and views in the data mart, we might be interested in the privileges we have on such objects. The sp_table_privileges_ex stored procedure shows us table rights.
/*
Show tables privileges
*/
-- Variable to table privileges
DECLARE @LsTablePriv TABLE
(
TABLE_CAT sysname NULL,
TABLE_SCHEM sysname NULL,
TABLE_NAME sysname NULL,
GRANTOR sysname NULL,
GRANTEE sysname NULL,
PRIVILEGE varchar(32) NULL,
IS_GRANTABLE varchar(3) NULL
);
-- Grab table privledges info
INSERT INTO @LsTablePriv
EXEC sp_table_privileges_ex
@table_server = N'MyAzureDb',
@table_catalog = N'pubs',
@table_schema = N'dbo',
@table_name = N'authors';
-- Show the information
SELECT * FROM @LsTablePriv
GO
Output from executing the code.
Primary Key Information
Database normalization is a process that organizes columns and tables so that data redundancy is reduced. A primary key is used to uniquely identify
a row of data in a particular table. Using primary keys in your database design is part of achieving 2nd Normal Form.
Why does the sp_primarykeys stored procedure return only one row per a given table?
A table can only have one primary key which can consist of one or more columns. A key with more than one column is called a composite key.
/*
Show primary key information
*/
-- Variable to hold PK info
DECLARE @LsPrimaryKeys TABLE
(
TABLE_CAT sysname NULL,
TABLE_SCHEM sysname NULL,
TABLE_NAME sysname NULL,
COLUMN_NAME sysname NULL,
KEY_SEQ int NULL,
PK_NAME sysname NULL
)
-- Grab primary key info
INSERT INTO @LsPrimaryKeys
EXEC sp_primarykeys
@table_server = N'MyAzureDb',
@table_catalog = N'pubs',
@table_schema = N'dbo',
@table_name = N'authors';
-- Show the information
SELECT * FROM @LsPrimaryKeys
GO
Output from executing the code.
Foreign Key Information
Foreign keys ensure relational integrity between a parent and a child table. This type of relationship is used in database normalization to reach 3rd Normal form.
In the Azure pubs database in the cloud, the author table has a many to many relationship with the titles table. This is achieved by using a
junction table named titleauthor.
Calling the sp_foreignkeys stored procedure allows a developer to discover any foreign key relationships.
/*
Show foreign key information
*/
-- Variable to hold FK info
DECLARE @LsForeignKeys TABLE
(
PKTABLE_CAT sysname NULL,
PKTABLE_SCHEM sysname NULL,
PKTABLE_NAME sysname NULL,
PKCOLUMN_NAME sysname NULL,
FKTABLE_CAT sysname NULL,
FKTABLE_SCHEM sysname NULL,
FKTABLE_NAME sysname NULL,
FKCOLUMN_NAME sysname NULL,
KEY_SEQ smallint NULL,
UPDATE_RULE smallint NULL,
DELETE_RULE smallint NULL,
FK_NAME sysname NULL,
PK_NAME sysname NULL,
DEFERRABILITY smallint NULL
);
-- Grab foreign key info
INSERT INTO @LsForeignKeys
EXEC sp_foreignkeys
@table_server = N'MyAzureDb',
@pktab_name = N'authors',
@pktab_catalog = N'pubs';
-- Show the information
SELECT * FROM @LsForeignKeys
GO
Output from executing the code.
Table Column Details
The next two system stored procedures deal with columns inside a table. They are very detailed in nature. I will provide a high level overview of them for completeness.
For more information, please refer to MSDN.
Calling the sp_columns_ex stored procedure will return detailed information for each column.
Things like data types, column size, precision and nullability are listed.
/*
Show table column information
*/
-- Variable to hold column info
DECLARE @LsColumns TABLE
(
TABLE_CAT sysname NULL,
TABLE_SCHEM sysname NULL,
TABLE_NAME sysname NULL,
COLUMN_NAME sysname NULL,
DATA_TYPE smallint NULL,
TYPE_NAME varchar(13) NULL,
COLUMN_SIZE int NULL,
BUFFER_LENGTH int NULL,
DECIMAL_DIGITS smallint NULL,
NUM_PREC_RADIX smallint NULL,
NULLABLE smallint NULL,
REMARKS varchar(254) NULL,
COLUMN_DEF varchar(254) NULL,
SQL_DATA_TYPE smallint NULL,
SQL_DATETIME_SUB smallint NULL,
CHAR_OCTET_LENGTH int NULL,
ORDINAL_POSITION int NULL,
IS_NULLABLE varchar(254) NULL,
SS_DATA_TYPE tinyint NULL
);
-- Grab column info
INSERT INTO @LsColumns
EXEC sp_columns_ex
@table_server = N'MyAzureDb',
@table_catalog = N'pubs',
@table_schema = N'dbo',
@table_name = N'authors';
-- Show the information
SELECT * FROM @LsColumns
GO
Output from executing the code.
Column Privileges Information
The sp_column_privileges_ex stored procedure returns security information at the column level.
The code below returns information on all columns in the authors database. By supplying the @column_name as a
parameter, we can limit the result set return by the call.
However, it is interesting to note that most database vendors do not support this advance feature.
/*
Show column privileges
*/
-- Variable to hold column info
DECLARE @LsColumnPrivledges TABLE
(
TABLE_CAT sysname NULL,
TABLE_SCHEM sysname NULL,
TABLE_NAME sysname NULL,
COLUMN_NAME sysname NULL,
GRANTOR sysname NULL,
GRANTEE sysname NULL,
PRIVILEGE varchar(32) NULL,
IS_GRANTABLE varchar(3) NULL
);
-- Grab column info
INSERT INTO @LsColumnPrivledges
EXEC sp_column_privileges_ex
@table_server = N'MyAzureDb',
@table_catalog = N'pubs',
@table_schema = N'dbo',
@table_name = N'authors'
--,
--@column_name = N'state';
-- Show the information
SELECT * FROM @LsColumnPrivledges
GO
Output from executing the code.
Index Information
The last system stored procedure for linked servers to talk about is sp_indexes.
A sample call the the procedure ignoring the returned values is below with the associated output.
Unfortunately, Microsoft broke this system stored procedure during the 2008 R2
to 2012 major version change.
While the catalog views can be used as a work around for this broken stored procedure, Microsoft should mark it as broken in Books On Line or place it on the maintenance work schedule.
/*
Show index information
*/
-- Show indexes (broken)
EXEC sp_indexes
@table_server = N'MyAzureDb',
@table_catalog = N'pubs',
@table_schema = N'dbo',
@table_name = N'authors',
@index_name = N'aunmind',
@is_unique = NULL;
Output from executing the code.
Next Steps
- Linked servers are only one way to move data back and forth between a on-premises and in-cloud database.
Since a linked server is implemented via a OLE DB provider, it sometimes lacks in performance.
Therefore, Linked Servers should be used for small to medium size information transfers.
How do I use BCP to speed up inserting data into the cloud?
About the author
John Miner is a Data Architect at Insight Digital Innovation helping corporations solve their business needs with various data platform solutions.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2015-05-26
