Oracle: Find out total number of records of all tables

An efficient method to find out the total record counts of all tables in an Oracle database.

select table_name,
  to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||owner||'.'||table_name)),'/ROWSET/ROW/C')) as count
from all_tables
where owner = 'SchemaName';

Hope it helps.

MSSQL: Find out total number of records of all tables

An efficient method to find out the total record counts of all tables in a MSSQL database.

SELECT tbl.name, CAST(p.rows AS int) 'count', tbl.schema_id, SCHEMA_NAME(tbl.schema_id) schema_name
FROM
  sys.tables AS tbl
  INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id AND idx.index_id < 2
  INNER JOIN sys.partitions AS p ON p.object_id = CAST(tbl.object_id AS int) AND p.index_id = idx.index_id
--WHERE ((SCHEMA_NAME(tbl.schema_id) = 'dbo'))
ORDER BY 2 DESC

Hope it helps.

Restore Database Backup Over a Network Drive

How do you restore a Microsoft SQL database backup over a network drive without having to download to local? Since the database server is run as a service under an account that often without network access permission, it wouldn't be possible to be able to select the network drive/path when you are performing a restore. Nonetheless, we are not left without workaround.

To restore database backup over a network, you will need to run the following SQL statements using your SQL Management Studio.

--enable to show advanced options
EXEC sp_configure 'show advanced options', 1
GO
--apply changes for show advanced options
RECONFIGURE
GO
--enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
--apply changes for xp_cmdshell
RECONFIGURE
GO
--replace network path, username and password accordingly
EXEC xp_cmdshell 'NET USE Z: \\SERVER\Path password /USER:Domain\Username'
GO

Once the above are executed, you should be able to choose Z:\ network drive to restore your database backup file. I hope it helps.

Installing Oracle Complaining Missing dr0ulib.sql.sbs

When installing Oracle Database 12c Release 1, you may encounter an error complaining missing dr0ulib.sql.sbs file. You will be stuck no matter how many times you hit on Retry button. It just won't proceed!

To resolve this issue, you will need to do this:

1. Abort installation.
2. Copy all folders and files in your \winx64_12102_SE2_database_2of2\database\stage\Components\ folder to \winx64_12102_SE2_database_1of2\database\stage\Components\.
3. Initiate installation again.

Please do not ask me why the need for this silly step. It is just how Oracle package their installation files. I would recommend them doing a ISO file instead of doing 2 ZIP files!

MsSQL: Shrink ALL Databases Using 1 Single Line of SQL Statement

Over time, the database logs on your database server may take up significant amount of disk space especially if there are many databases with lots of transactions. One quick method to free up disk space is to "shrink" your databases. Instead of shrinking one database at a time using the GUI, you can run a single line of SQL statement to do just that.

EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE (''?'' , 0)'

Hope it helps.

Rebuild all Indexes on a Microsoft SQL Server

As part of database maintenance, a DBA may wish to do periodical rebuild of all indexes. The below may come handy.

EXEC sp_MSForEachTable 'ALTER INDEX ALL ON ? REBUILD'

MsSQL Split Function

The following demonstrates how to do a string Split using a character delimiter.

Edited: [dbo].[Split_PerformanceEnhanced] added - better performance results (31 July 2017).

--SQL function to do split (performance enhanced)
CREATE FUNCTION [dbo].[Split_PerformanceEnhanced]
(
 @List NVARCHAR(MAX),
 @Delimiter VARCHAR(5)
)  
RETURNS @RtnValue TABLE
(
 ID INT IDENTITY(1,1),
 Data VARCHAR(MAX)
) 
AS
BEGIN
 INSERT INTO @RtnValue(Data)
  SELECT [Value] FROM 
    ( 
    SELECT 
     [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
     CHARINDEX(@Delimiter, @List + @Delimiter COLLATE Latin1_General_100_BIN2, [Number]) - [Number])))
    FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
     FROM sys.all_objects) AS x
     WHERE Number <= LEN(@List)
     AND SUBSTRING(@Delimiter + @List, [Number], LEN(@Delimiter)) = @Delimiter
    ) AS y

 RETURN;
END
GO
--Test out the SQL function
SELECT * FROM dbo.Split_PerformanceEnhanced('one|two|three|four', '|')
GO

Below SQL function still works but prefer the above new performance enhanced version.

--SQL function to do split
CREATE FUNCTION [dbo].[Split]
(
 @RowData NVARCHAR(MAX),
 @SplitOn CHAR(1)
)  
RETURNS @RtnValue TABLE
(
 ID INT IDENTITY(1,1),
 Data VARCHAR(MAX)
) 
AS
BEGIN
 DECLARE @count INT
 SET @count = 1

 WHILE (CHARINDEX(@SplitOn COLLATE Latin1_General_100_BIN2, @RowData) > 0)
 BEGIN
  INSERT INTO @RtnValue (data)
  SELECT Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, CHARINDEX(@SplitOn COLLATE Latin1_General_100_BIN2, @RowData) - 1)))
  SET @RowData = SUBSTRING(@RowData, CHARINDEX(@SplitOn COLLATE Latin1_General_100_BIN2, @RowData) + 1, LEN(@RowData))
  SET @count = @count + 1
 END

 INSERT INTO @RtnValue (data)
 SELECT Data = LTRIM(RTRIM(@RowData))

 RETURN
END
GO
--Test out the SQL function
SELECT * FROM dbo.Split('one|two|three|four', '|')
GO

How do I remove the cached username and password from SQL Management Studio?

You may have your reasons to want to remove the cached username and password from the SQL Management Studio e.g. before handing your laptop to someone else.

The step to remove will require deleting a file but differs depending on the version of SQL Management Studio you are using.

SQL Server Management Studio 2014
Delete C:\Users\%username%\AppData\Roaming\Microsoft\SQL Server Management Studio\12.0\SqlStudio.bin

SQL Server Management Studio 2012
Delete C:\Users\%username%\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0\SqlStudio.bin

SQL Server Management Studio 2008
Delete C:\Users\%username%\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

SQL Server Management Studio 2005
Delete C:\Users\%username%\AppData\Roaming\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat

One good news is as of SQL Server Management Studio 2012, you may delete an entry by using the tool itself without the need to delete a file. To delete, you will need to click on the 'Server name' dropdown in the 'Connect to Server' dialog. Move your mouse or use your keyboard arrow key to highlight the server to remove followed by hitting on the 'Delete' key on your keyboard. This is so convenient!

An Introduction To Node.js And MongoDB

A comprehensive article on Node.js and MongoDB.

MSSQL: Get All Stored Procedures With References to a Table

AFAIK, there is no built-in feature to generate a list of stored procedures that made references to a specific database table. The following help me to do just that.

SELECT DISTINCT so.name
FROM syscomments sc INNER JOIN sysobjects so ON sc.id = so.id AND so.xtype = 'P'
WHERE sc.TEXT LIKE '%TABLE_NAME%'

MSSQL: Generate Random String Using SQL

The following will generate a random fixed-length string using SQL. Simply pass in a string of accepted characters and the length of the random string to be generated.

IF ISNULL(object_id('cspRandomString'),0)<>0 DROP PROC cspRandomString
IF ISNULL(object_id('fnGenRandomInteger'),0)<>0 DROP FUNCTION fnGenRandomInteger
GO
CREATE function fnGenRandomInteger(@MinValue int, @MaxValue int, @Seed float)
returns int
as
begin
-- =============================================
-- Author:        Loh Hon Chun
-- =============================================
 return ((@MaxValue + 1) - @MinValue) * @Seed + @MinValue
end
go
CREATE procedure cspRandomString (
 @Result varchar(255) output,
 @AcceptedChars varchar(255),
 @Length int
)
as
begin
-- =============================================
-- Author:        Loh Hon Chun
-- =============================================
 set nocount on

 declare @ret char(255)
 declare @i int, @pos int
 
 set @Result = ''
 set @i = 0

 if LEN(@AcceptedChars) > 0
 begin
  while @i < @Length
  begin
   set @pos = dbo.fnGenRandomInteger(1, LEN(@AcceptedChars), RAND(CONVERT(VARBINARY, NEWID())))
   set @Result = @Result + CAST(SUBSTRING(@AcceptedChars, @pos, 1) AS VARCHAR(1))
   set @i = @i + 1
  end
 end
end
go

declare @result varchar(255)
declare @acceptedChars varchar(255)
declare @length int

set @acceptedChars = '0123456789'
set @length = 7
exec cspRandomString @Result=@result output, @AcceptedChars=@acceptedChars, @Length=@length
print @result
go

MSSQL: Run Large SQL Script

Having problems when running large SQL scripts using the SQL Management Studio (SSMS)? I would suggest you use sqlcmd instead. Save your script in a flat file (e.g. .sql or .txt) and pass the file as an input parameter to sqlcmd like below.

sqlcmd -S SERVER\INSTANCE -i mysqlscript.sql

In case you are having problems logging in, you may wish to try either one of them.

sqlcmd -S SERVER\INSTANCE,PORT -i mysqlscript.sql

OR

sqlcmd -S np:\\127.0.0.1\pipe\MSSQL$INSTANCE\sql\query -i mysqlscript.sql

NOTE: Replace SERVER, INSTANCE, PORT accordingly to suit your server configuration.

Hope it helps.

MSSQL: Update running sequence / number

Let's say we have a table with a column of numbers and for some reason, they are not in running sequence. Instead of editing the rows manually, we can actually do it using a SQL script.

Hopefully the following helps.

--Create some test data
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'tblTest') AND type in (N'U')) DROP TABLE tblTest
GO
CREATE TABLE tblTest (Label VARCHAR(20), OriginalOrderNo INT, OrderNo INT)
INSERT INTO tblTest (Label, OriginalOrderNo, OrderNo) VALUES ('four', 4, 4)
INSERT INTO tblTest (Label, OriginalOrderNo, OrderNo) VALUES ('seven', 7, 7)
INSERT INTO tblTest (Label, OriginalOrderNo, OrderNo) VALUES ('one', 1, 1)
INSERT INTO tblTest (Label, OriginalOrderNo, OrderNo) VALUES ('two', 2, 2)
INSERT INTO tblTest (Label, OriginalOrderNo, OrderNo) VALUES ('five', 5, 5)
INSERT INTO tblTest (Label, OriginalOrderNo, OrderNo) VALUES ('nine', 9, 9)
GO
--BEFORE
SELECT * FROM tblTest ORDER BY OrderNo
GO
--DO the UPDATE
UPDATE t1 SET t1.OrderNo = t2.Sequence
FROM tblTest t1 INNER JOIN (
 SELECT Label, OrderNo, ROW_NUMBER() OVER (ORDER BY OrderNo) AS 'Sequence'
 FROM tblTest) AS t2 ON t1.OrderNo = t2.OrderNo
GO
--AFTER
SELECT * FROM tblTest ORDER BY OrderNo
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'tblTest') AND type in (N'U')) DROP TABLE tblTest
GO

MSSQL: Get schema owner of database tables

To retrieve a list of schema owner of database tables, try the following code snippet. Hope it help.s

SELECT t.object_id AS 'TableID', t.name AS 'TableName', s.schema_id AS 'SchemaID', s.name AS 'SchemaOwner'
FROM sys.tables t inner join sys.schemas s ON t.schema_id = s.schema_id
ORDER by t.name

MSSQL: sp_MSforeachdb to loop through all databases without using cursors

sp_MSforeachdb is an undocumented stored procedure since SQL 2005 that can come in real handy, looping through all databases to get information without having to write cursors. The idea of its usage is to use the [?] placeholder and SQL will intelligently do a replace with a database name.

Just some sample usage of sp_MSforeachdb.

--Get all table/columns information from all databases
sp_MSforeachdb 'SELECT * FROM [?].[INFORMATION_SCHEMA].[COLUMNS]'

--Check for orphans login
EXECUTE sp_MSforeachdb 'sp_change_users_login @Action=''report'''

--Get database information - space, name
EXECUTE sp_MSforeachdb 
 'SELECT
  ServerName=@@SERVERNAME, DBName=DB_NAME(), [fileid], CONVERT(DECIMAL(12,2),ROUND([size]/128.000,2)) AS ''SizeMB'',
  SpaceUsedMB = CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY([name],''SpaceUsed'')/128.000,2)),
  FreeSpaceMB = CONVERT(DECIMAL(12,2),ROUND(([size]-FILEPROPERTY([name],''SpaceUsed''))/128.000,2)) ,
  name, filename
  FROM dbo.sysfiles'

Undocumented may mean it may not be supported in future versions. My last check is sp_MSforeachdb still works for SQL 2008.

Related reading:

1. MSSQL: sp_MSforeachtable to loop through all tables without using cursors

MSSQL: sp_MSforeachtable to loop through all tables without using cursors

sp_MSforeachtable is an undocumented stored procedure since SQL 2005 that can come in real handy, looping through all tables in a database to get information without having to write cursors. The idea of its usage is to use the [?] placeholder and SQL will intelligently do a replace with a table name.

Just some sample usage of sp_MSforeachtable.

--Check space used for each table in a database
EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?]';

--Check for integrity of tables in a database
EXECUTE sp_MSforeachtable 'DBCC CHECKTABLE ([?])'

Undocumented may mean it may not be supported in future versions. My last check is sp_MSforeachtable still works for SQL 2008.

Related reading:

1. MSSQL: sp_MSforeachdb to loop through all databases without using cursors

SQL Management Studio - "Saving changes is not permitted ..." error

Sometimes when you are working something using SQL Management Studio (SSMS) and intend to make changes to existing table schema, e.g. adding a new column, you may encounter the following error message. You are unable to proceed making the changes you desire with SSMS user interface.

Saving changes is not permitted ...

So what can you do? Either you re-create the table or write ALTER statements (but let's not do that!).

There is, however, a known trick to get around the above annoying "Saving changes is not permitted ..." error. Here are the steps to do just that!

1. From the SSMS, go to Tools > Options.
2. Select the Designers page.
3. Un-check "Prevent saving changes that require table re-creation".
   

   

   Options Box

4. Click OK button.

You should be good to go now!

How to delete the server history list from SQL Management Studio 2008?

Especially if you are in an environment where you need to connect to several different DB servers, the list of server names in your SQL Management Studio (SSMS) may get lengthy and some may even become defunct.

The question is how do I delete them? The steps involved is actually quite simple (note this is for SSMS 2008).

1. Go to %UserProfile%\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell
2. Find SqlStudio.bin file and delete it.

The server name list is now clean again.

SQL Management Studio 2008

How to check 32 bit OLE DB providers installed on a 64 bit machine?

One method to check the OLE DB providers installed on your machine is to create an UDL file and run it. However, if you are running a 64 bit operating system, running the UDL file will list all the 64 bit OLE DB providers installed and not the 32 bit ones.

To enumerate and show the 32 bit OLE DB providers, perform the following steps:

1. Create a UDL file with the name test.udl under the path, C:\.

2. To list the 32-bit OLE DB providers installed on a 64 bit operating system, run the below command on Command Line.

C:\Windows\SysWOW64\rundll32.exe "C:\Program Files (x86)\Common Files\System\Ole DB\oledb32.dll", OpenDSLFile C:\test.udl

NOTE: If we simply double click on the UDL file instead of running the above command on command line, it will list out 64 bit OLE DB providers by default.

» How to run 32-bit UDL file on a 64-bit Operating System | Chaitanya's Blogs -- SQL Developer findings

Combine UPDATE and SELECT in one statement

After we did an UPDATE on a record and when we want to find out what are updated, we will usually follow up with a SELECT. Example,

update tblTest
set
    Age = Age + 1, LastModifiedDate = GETDATE()
where Name = 'Name 1'

select * from tblTest
where Name = 'Name 1'

How about doing the above all in one single statement and avoiding the select statement from getting outdated record when someone intercepts and attempts to update?

It's possible! Try the following.

--SETUP
if ISNULL(object_id('tblTest'),0)<>0 drop table tblTest
go
create table tblTest (
    ID int identity(1,1) not null primary key,
    Name varchar(255) not null,
    Age tinyint not null,
    LastModifiedDate datetime not null default getdate()
)
go

--INSERT test records
insert into tblTest (Name, Age) values ('Name 1', 21)
insert into tblTest (Name, Age) values ('Name 2', 22)
go

--CREATE the temp table for select
if ISNULL(object_id('tempdb..#tem_tblTest'),0)<>0 drop table #tem_tblTest
create table #tem_tblTest (ID int, Name varchar(255), Age tinyint)

--UPDATE record and select into temp table (All in one statement)
update tblTest
set
    Age = Age + 1, LastModifiedDate = GETDATE()
output inserted.ID, inserted.Name, inserted.Age into #tem_tblTest (ID, Name, Age)
where Name = 'Name 1'

--SELECT what's updated
select * from #tem_tblTest
go

--CLEANUP
if ISNULL(object_id('tempdb..#tem_tblTest'),0)<>0 drop table #tem_tblTest