Tag: SQL | Page 2

How to Get Table Definition in SQL Server

Whenever you need to pull a quick definition of a table in SQL Server. Here’s the SQL command (T-SQL) to get the table definition in a database:

USE [MyDB]
GO

SELECT COLUMN_NAME,
  CASE
    WHEN ISNULL(NUMERIC_PRECISION, 200) <> 200 THEN DATA_TYPE + '('
         + CAST(NUMERIC_PRECISION AS VARCHAR(5))
         + ',' + CAST(NUMERIC_SCALE AS VARCHAR(5))
         + ')'
    WHEN ISNULL(CHARACTER_MAXIMUM_LENGTH, 0) = 0 THEN DATA_TYPE
  ELSE
    DATA_TYPE + '('
      + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))
      + ')'
  END AS DATA_TYPE,
  IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '[MyTable]'
GO

The result would be something similar to this:

COLUMN_NAME	DATA_TYPE	IS_NULLABLE
AccountNo	int(10,0)	YES
AccountName	varchar(50)	YES
Balance	money(10,2)	YES
UpdateDate	datetime	NO

You need to substitute [MyDB] and [MyTable] to the database name and table name that you’re querying. That’s all there is to it.

How to Import IIS Log to PostgreSQL

If you ever had a need to dump and analyze IIS logs from a database, this post will show you how to do it in PostgreSQL RDBMS.

The assumption is that you’re already familiar with the default installation and setup of PostgreSQL, if not please check the manual link at the bottom of this post. We will use default settings.

Steps to Create IIS Log Table and Import Log File to PostgreSQL

Create a database called mydb.

We need to prepare a table that matches the fields from IIS log, so we need to create a table called iislog by running the following SQL:

CREATE TABLE public.iislog
(
    date date,
    "time" time without time zone,
    sip character varying(48) COLLATE pg_catalog."default",
    csmethod character varying(8) COLLATE pg_catalog."default",
    csuristem character varying(255) COLLATE pg_catalog."default",
    csuriquery character varying(2048) COLLATE pg_catalog."default",
    sport character varying(4) COLLATE pg_catalog."default",
    susername character varying(256) COLLATE pg_catalog."default",
    cip character varying(48) COLLATE pg_catalog."default",
    csuseragent character varying(1024) COLLATE pg_catalog."default",
    csreferer character varying(4096) COLLATE pg_catalog."default",
    scstatus integer,
    scsubstatus integer,
    scwin32status bigint,
    timetaken integer
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.iislog
    OWNER to postgres;

Go to the location of where the IIS log files are stored. By default, the location is in the following folder:
```
%SystemDrive%\inetpub\logs\LogFiles
```
However, as the best practice, most enterprise applications will have a different location on a different drive for log. In this example, we’ll use C:\Temp\IISLogs\ folder.
We will certainly not going to import each log file one-by-one to the database, so the best thing to do before we do the import is to combine all the log files we want to analyze. Just for the sake of simplicity for this demonstration, we want to include all log files within this folder and combine them into one big log file. We can do this by opening either a Command Prompt or PowerShell Windows. It doesn’t matter which one you prefer, it will work the same way.
In the Command Prompt or PowerShell Window, make sure you’re in the IIS log folder and type in the following command:
Command only
```
type *.log > filename.log
```
Command with prompt (as in this example)
```
C:\Temp\IISLog> type *.log > combinedLogs.log
```
*The prompt is included to show that you need to be in your IIS log folder to execute this operation.
Now we need to massage the combined log file a bit before we can import it within one operation.
If you open the new log file (combinedLogs.log) with a text editor, you will see all the headers from every single log file were included as well and it looks something similar to this:
```
#Software: Microsoft Internet Information Services 10.0
#Version: 1.0
#Date: 2018-08-02 15:08:58
#Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) cs(Referer) sc-status sc-substatus sc-win32-status time-taken
```

By using a text editor such as Sublime Text, we can clean up this log file by using Regex Find and Replace.

From the top menu, go to Find and Replace… (CTRL-H in Windows) and make sure the Regex option is activated and type (or copy and paste) the following search string in the Find: box:

#Software: Microsoft Internet Information Services 10.0\n#Version: 1.0\n#Date: [0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}\n#Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs\(User-Agent\) cs\(Referer\) sc-status sc-substatus sc-win32-status time-taken\n

Press Replace All button to remove all instances of those headers and re-save the file.
Now we will import this log file by using COPY command.
Go back to PostgreSQL shell and type in the following:
```
COPY public.iislog FROM 'c:\temp\iislogs\combinedLogs.log' DELIMITER ' ';
```
*If you use a different table name and location of IIS log, make sure you change it to match yours.

That’s all there is, now you should be able to query the log and slice and dice the data however you need it.

Caveat

Important: If you have a rather big combined log file (even only a few MB in size), make sure you do the import from the command line. Do not use pgAdmin web interface as it may not be able to handle the load.

Download

PostgreSQL

How to Show Field Values as Columns in SQL Server

One of the most asked questions by users when they pull data using T-SQL is whether or not there’s a way to present the values of a field they’re interested in as columns instead. The answer is of course it’s “Yes.” You can achieve this by using PIVOT relational operator if you use MSSQL 2005 or later.

Without PIVOT

USE [MyDB];

--WITHOUT PIVOT
SELECT CostCenter, [Type], COUNT(AssetTag) AS Total FROM tblInventory
WHERE [Type] = 'Desktop'
GROUP BY CostCenter, [Type]
UNION
SELECT CostCenter, [Type], COUNT(AssetTag) AS Total FROM tblInventory
WHERE [Type] = 'Laptop'
GROUP BY CostCenter, [Type]
ORDER BY CostCenter;

With PIVOT

USE [MyDB];

--WITH PIVOT
SELECT CostCenter, Desktop, Laptop FROM (
SELECT CostCenter, [Type], COUNT(AssetTag) AS Total FROM tblInventory
WHERE [Type] = 'Desktop'
GROUP BY CostCenter, [Type] 
UNION
SELECT CostCenter, [Type], COUNT(AssetTag) AS Total FROM tblInventory
WHERE [Type] = 'Laptop'
GROUP BY CostCenter, [Type] 
) t1
PIVOT
(SUM(Total) FOR [Type] in ([Desktop], [Laptop])) t2
ORDER BY CostCenter;

How to reset SA password on Microsoft SQL Server

If you happen to forget your sa password, you can still recover it as long as you have access to the server. Here are the steps to do it.

Steps

Launch Sql Configuration Manager under Configuration Tools folder.
Look for your SQL Server instance (the default is MSSQLSERVER) and stop the service. You can click the stop button while having the SQL Server (MSSQLSERVER) row highlighted or you can right-click on it and select Stop.
Launch the Command Prompt.
Next, we want to run the SQL Server in a single-user mode by adding “/m” parameter with the client application name:
net start MSSQLSERVER /m"SQLCMD"
Then we need to connect to the database on the machine using a trusted connection:
sqlcmd -E -S localhost
If you’re connecting to a database on a local machine, you can substitute “localhost” with a “.” (dot), which makes it look like so:
sqlcmd -E -S . or sqlcmd -E -S.
The two are identical except the former is easier to read. Another note is if you’re using SQL Server Express, you need to add “\SQLEXPRESS” after the period. You can see the difference on the example below.
After starting the sqlcmd, type the following SQL statement after the prompt. There is a difference in role assignment between SQL Server 2008 and SQL Server 2012:
For SQL Server 2008 or Older
```
                CREATE LOGIN tempUser WITH PASSWORD = 'N3wPa$$1'
                GO
                sp_addsrvrolemember 'tempUser', 'sysadmin'
                GO
                
```
For SQL Server 2012 or Later
```
                CREATE LOGIN tempUser WITH PASSWORD = 'N3wPa$$1'
                GO
                ALTER SERVER ROLE sysadmin ADD MEMBER tempUser
                GO
                
```
For SQL Server 2012 or newer, use ALTER SERVER ROLE should be used instead of sp_addsrvrolemember as this system stored procedure will be removed in a future version of Microsoft SQL Server.
You can type “exit” to quit SQLCMD.
Restart the Sql Server service to get out of the single-user mode:
net stop MSSQLSERVER followed by net start MSSQLSERVER
Launch SQL Server Management Studio and connect to the local database using the new login you just created.
Expand on Security, then expand on Logins.
Right-click on user sa and select Properties. Enter the new password and click OK. And you’re done.

Now you can login to the database using the sa login and the new password you set. For security purpose, make sure you delete the tempUser afterwards.

Ways to Upsert a Record in SQL Server

To continue the previous post, this article demonstrates ways to do Upsert (update and insert) and how MERGE statement in SQL Server 2008 (or later) can be more efficient to perform the two operations at once.

First we’ll create a table for this demo.

CREATE TABLE dbo.GroupInfo (
    Id    int unique not null
  , App   varchar(100)
  , DB    bit
)

We want to do update if the Id is found on the table and insert if it’s a new Id number.

1. Conventional way of doing it is by using IF EXISTS statement.

CREATE PROCEDURE [dbo].[p_UPSERT1]
    @ID     int
  , @APP    varchar(100)
  , @DB     bit
AS
SET NOCOUNT ON;
IF EXISTS (SELECT Id FROM dbo.GroupInfo WHERE Id = @ID)
  UPDATE dbo.GroupInfo
  SET
      App = @APP
    , DB = @DB
  WHERE Id = @ID
ELSE
  INSERT INTO dbo.GroupInfo (
      Id
    , App
    , DB
  ) VALUES (
        @ID
    , @APP
    , @DB
  )
  SET NOCOUNT OFF;

2. Second way of doing it is by taking advantage of the @@ROWCOUNT.

CREATE PROCEDURE [dbo].[p_UPSERT2]
    @ID     int
  , @APP    varchar(100)
  , @DB     bit
AS
SET NOCOUNT ON;
UPDATE dbo.GroupInfo
  SET
      App = @APP
    , DB = @DB
WHERE Id = @ID
IF @@ROWCOUNT = 0
  INSERT INTO dbo.GroupInfo (
      Id
    , App
    , DB
  ) VALUES (
      @ID
    , @APP
    , @DB
  )
SET NOCOUNT OFF;

3. The third and probaby the best way by using MERGE to perform INSERT and UPDATE operations on a table in a single statement.

CREATE PROCEDURE [dbo].[p_UPSERT3]
    @ID     int
  , @APP    varchar(100)
  , @DB     bit
AS
SET NOCOUNT ON;
MERGE INTO dbo.GroupInfo AS tgt
USING
  (SELECT @ID) AS src (id)
  ON tgt.Id = src.id
WHEN MATCHED THEN
  UPDATE        
    SET
        App = @APP
      , DB = @DB
WHEN NOT MATCHED THEN
  INSERT (
      Id
    , App
    , DB
  ) VALUES (
      @ID
    , @APP
    , @DB
  );
SET NOCOUNT OFF;

Now, you can analyze the execution plan of each stored procedure on your own to compare them.

EXEC p_UPSERT1
  @ID = 1
, @APP = 'App 1'
, @DB = 0
GO

EXEC p_UPSERT2
  @ID = 2
, @APP = 'App 2'
, @DB = 0
GO

EXEC p_UPSERT3
  @ID = 3
, @APP = 'App 3'
, @DB = 0
GO

How to Get Table Definition in SQL Server

Further Reading

How to Import IIS Log to PostgreSQL

Steps to Create IIS Log Table and Import Log File to PostgreSQL

Command only

Command with prompt (as in this example)

Caveat

Further Reading

Download

How to Show Field Values as Columns in SQL Server

Without PIVOT

With PIVOT

Further Reading

How to reset SA password on Microsoft SQL Server

Steps

For SQL Server 2008 or Older

For SQL Server 2012 or Later

Further Reading

Ways to Upsert a Record in SQL Server

Further Reading

Recent Posts

Further Reading

Steps to Create IIS Log Table and Import Log File to PostgreSQL

Command only

Command with prompt (as in this example)

Caveat

Further Reading

Download

Without PIVOT

With PIVOT

Further Reading

Steps

For SQL Server 2008 or Older

For SQL Server 2012 or Later

Further Reading

Further Reading

Recent Posts

Tags