Monthly Archives: February 2010

Get the Record Count for all the Tables in a Database in SQL Server

Often we take the COUNT of records in a table or many of the times all the tables in a database. We might need to get the COUNT of records in all the tables especially for validation purposes.
 
For instance when you are loading the data into your Staging Database in an incremental fashion, you need to do few checks to make sure that the incremental logic is working fine. As part of this, one of the most basic checks is to first get the COUNT of records from all the tables in Source & Staging Databases and compare the COUNTs.
 
Here is a very simple way to get the COUNT of records from all the tables in a database. Run the following query in the database in which you need to get the COUNTs of all the tables.
 
DECLARE @QueryString NVARCHAR(MAX)SELECT @QueryString = COALESCE(@QueryString + ‘ UNION ALL ‘,) + ‘SELECT ‘ + ”” + TABLE_SCHEMA + ‘.’ + TABLE_NAME + ”” + ‘ AS TableName, COUNT(1) AS RecordCount FROM ‘ + TABLE_SCHEMA + ‘.’ + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE’
ORDER BY TABLE_SCHEMA, TABLE_NAMEEXEC Sp_executesql @QueryString

When you run the above query in AdventureWorksDW database then the results will be as shown below (Tables/Counts might slightly vary depending on the version of AdventureWorksDW). 

Advertisements

Error – The path is not valid. The path must confirm to Uniform Naming Conventions (UNC) format.

Recently I was working on Standard Subscriptions feature of SSRS, which is a really great feature in SSRS.
 
There are basically two types of Subscriptions – Standard and Data-Driven Subscriptions.  Each of these Subscription types offer delivery options like Delivery via E-Mail, Delivery to a specified location on the File System etc. These Subscriptions also offer various rendering/delivery formats like Excel, PDF etc. Basically all the exporting formats that are available in Report Server/Manager. However getting into the details of Subscriptions is out of scope of this article. More information on this topic can be found on msdn at the following location:
 
 
Today I was working on Subscription with Windows File Share/File System delivery option. While configuring this option, we need to specify the location to which the report need to be delivered/saved on the file system. I gave the location for delivery as a local drive on my personal computer.
 
When I gave the local path as “F:\Datta\MyTechnoBook – Files\” and got an error as
 
The path is not valid. The path must confirm to Uniform Naming Conventions (UNC) format.
 
 
 
This error indicates that the path should be provided in the form of Uniform Naming Conventions (UNC) format. Or in simple terms we need to provide the path in the format of Shared Drive as follows:
 
 
When I gave the location as “\\SINDOL\F$\Datta\MyTechnoBook – Files\” it solved the error.
 
Hope you find this article helps! If yes then do leave your comments 🙂

Copy Table Structure With Or Without Data in SQL Server

Often while working with T-SQL we create copies of tables as a backup of the original/base table so that we can make changes to the data in the original/base table for testing purposes, and revert back to the backup table if needed. SQL Server provides few Key Words or options to perform this operation.
 
However there are few key differences to note between the original table and the backup table while creating the copies of the tables With/Without data, and these differences could be really important to consider especially when we decide to drop the original table since you have taken the backup. Before you drop the original table, be careful and think twice before you do this! Read through this article to know more about the differences between the Original Table and the Copy of the table.
 
Now in this article lets take a look at how to create a copy of a table in No Time in either of the following ways:
  • Copy Table Structure With Data
  • Copy Only Table Structure Without Data
Create a sample table as follows and call it as ExistingEmployeeTable.
/* Create Table */
CREATE TABLE ExistingEmployeeTable (

EmployeeId INT IDENTITY (1,1) NOT NULL,
EmployeeName NVARCHAR(100) NOT NULL,
ManagerId INT)
GO

Create a Primary Key on EmployeeId.

/* Create Primary Key */
ALTER TABLE ExistingEmployeeTable

ADD CONSTRAINTPK_ExistingEmployeeTable_EmployeeId PRIMARY KEY (EmployeeId)
GO

Now create another table called ExistingManagerTable with ManagerId as Primary Key.

/* Create Table */
CREATE TABLE ExistingManagerTable (

ManagerId INT IDENTITY (1,1) NOT NULL,
ManagerName NVARCHAR(100) NOT NULL)
GO

/* Create Primary Key */
ALTER TABLE ExistingManagerTable
ADD CONSTRAINT PK_ExistingManagerTable_ManagerId PRIMARY KEY (ManagerId)
GO

Now create a Foreign Key table in ExistingEmployeeTable on ManagerId and also create a Non Clustered Index on ManagerId.

/* Create Foreign Key */
ALTER TABLE ExistingEmployeeTable
ADD CONSTRAINTFK_ExistingEmployeeTable_ManagerId FOREIGN KEY (ManagerId) REFERENCES ExistingManagerTable(ManagerId)
GO


Now lets take a look at the structure of the two newly created tables as below.
/* Create Non Unique Non Clustered Index */
CREATE NONCLUSTERED INDEX IDX_NU_NCL_ExistingEmployeeTable_ManagerId ONExistingEmployeeTable(ManagerId)
GO

Figure 1.0

INSERT INTO ExistingManagerTable(ManagerName)
SELECT ‘John’ AS ManagerName
UNION
SELECT ‘James’ AS ManagerName
UNION
SELECT ‘Michael’ AS ManagerName
GO

INSERT INTO ExistingEmployeeTable(EmployeeName, ManagerId)
SELECT ‘Robert’ AS EmployeeName, 1 AS ManagerId
UNION
SELECT ‘Daniel’ AS EmployeeName, 2 AS ManagerId
UNION
SELECT ‘David’ AS EmployeeName, 3 AS ManagerId
UNION
SELECT ‘Steven’ AS EmployeeName, 2 AS ManagerId
UNION
SELECT ‘Albert’ AS EmployeeName, 3 AS ManagerId
GO


Now lets create a copy of the ExistingEmployeeTable along With Data as follows. You can either create a Physical Table or a Temporary Table based on your needs.

/* Create Copy of Table With Data */
SELECT *
INTO dbo.NewEmployeeTable
FROM dbo.ExistingEmployeeTable


/* Create Copy of Table Without Data */
SELECT *
INTO dbo.#NewEmployeeTable
FROM dbo.ExistingEmployeeTable
WHERE 1 = 2

In this query SQL Server creates a copy of the table structure, but since the condition in the WHERE clause is FALSE, the data will not be copied to the newly created table.

Now lets take a look at the structure of the backup/newly created NewEmployeeTable table.You can also create a copy of the ExistingEmployeeTable Without Data as follows. Again here you can create either a Physical Table or a Temporary Table.

 

Figure 2.0

Now compare the table structures of ExistingEmployeeTable (Old Table) & NewEmployeeTable (New Table), and here are few of the key differences to note:

  • Primary & Foreign Keys are not created in the Newly Created Table (NewEmployeeTable)
  • Indexes are not created in the Newly Created Table (NewEmployeeTable)
  • Default/Check Constraints are not created in the Newly Created Table (NewEmployeeTable)

These are few most important differences which one should note before deciding to drop the original table after taking a backup of the table using SELECT * INTO statement.

Reference: Dattatrey Sindol (http://mytechnobook.blogspot.com/2010/02/how-to-copy-table-structure-with-or.html)