System Stored Procedures are useful in performing administrative and informational activities in SQL Server. Here’s a bunch of System Stored Procedures that are used on a frequent basis (in no particular order):
System Stored Procedure
Description
sp_help Reports information about a database object, a user-defined data type, or a data type
sp_helpdb Reports information about a specified database or all databases
sp_helptext Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure
sp_helpfile Returns the physical names and attributes of files associated with the current database. Use this stored procedure to determine the names of files to attach to or detach from the server
sp_spaceused Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database
sp_who Provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session
sp_lock Reports information about locks. This stored procedure will be removed in a future version of Microsoft SQL Server. Use the sys.dm_tran_locks dynamic management view instead.
sp_configure Displays or changes global configuration settings for the current server
sp_tables Returns a list of objects that can be queried in the current environment. This means any object that can appear in a FROM clause, except synonym objects.
sp_columns Returns column information for the specified tables or views that can be queried in the current environment
sp_depends Displays information about database object dependencies, such as the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure. References to objects outside the current database are not reported
These were some System Stored Procedures in SQL Server that come in very handy. If you have been using any other system stored procedures (not listed here) on a frequent basis, feel free to share them via the comments section.
Wednesday, 22 December 2010
Find Number of Weeks in a Month using SQL Serv
I have been asked this question plenty of times – How do I calculate the number of weeks in a month. The answer to this query depends on how do you define a week. For simplicity purposes, I will take a week from Sunday to Saturday as depicted in the calendar
So November has 5 weeks!
Let us write the query to calculate the number of weeks in each month of this year. This query was originally written by Michael Jones and I have modified it to suit the requirement
DECLARE @Yr SMALLINT
SET @Yr = 2009
;WITH NumWeeks
AS
(
SELECT Number + 1 as mth,
DATEDIFF(day,-1,DATEADD(month,((@Yr-1900)*12)+ Number,0))/7 AS fst,
DATEDIFF(day,-1,DATEADD(month,((@Yr-1900)*12)+ Number,30))/7 AS lst
FROM master..spt_values
WHERE Type = 'P' and Number < 12
)
SELECT DateName(mm,DATEADD(mm,mth,-1)) as [MonthName],
lst - fst + 1 AS [NumberOfWeeks]
FROM NumWeeks;
OUTPUT
So November has 5 weeks!
Let us write the query to calculate the number of weeks in each month of this year. This query was originally written by Michael Jones and I have modified it to suit the requirement
DECLARE @Yr SMALLINT
SET @Yr = 2009
;WITH NumWeeks
AS
(
SELECT Number + 1 as mth,
DATEDIFF(day,-1,DATEADD(month,((@Yr-1900)*12)+ Number,0))/7 AS fst,
DATEDIFF(day,-1,DATEADD(month,((@Yr-1900)*12)+ Number,30))/7 AS lst
FROM master..spt_values
WHERE Type = 'P' and Number < 12
)
SELECT DateName(mm,DATEADD(mm,mth,-1)) as [MonthName],
lst - fst + 1 AS [NumberOfWeeks]
FROM NumWeeks;
OUTPUT
SQL Server 2008: Merge Statement
SQL Server 2008 introduced the Merge statement which is commonly also called as Upsert (Update / Insert). It can be used as Insert if the row does not exists and update, if it exists. It can be used as ETL process while fetching data. This feature is useful for developers. It is not required to give any particular join (inner, left outer etc).
Let’s see some advantages for using Merge statement.
With Merge statement the ability to insert a row if it does not exist or to update if it exists, is provided with a single statement. With previous version of SQL it was required to create separate statements for insert, update or delete to be done. With Merge statement, a source table (or a query) is allowed to join with target table (or updatable view) based on a criteria. Depending upon the match of the criteria specified update, insert or delete can be used. There are 3 options in the condition WHEN MATCHED, WHEN TARGET NOT MATCHED, WHEN SOURCE NOT MATCHED.
The syntax of Merge statement is as follows :
MERGE source
USING
(SELECT * FROM TARGET)
ON criteria
WHEN MATCHED
THEN UPDATE/DELETE
WHEN TARGET NOT MATCHED
THEN INSERT
WHEN SOURCE NOT MATCHED
THEN UPDATE/DELETE
Let’s see some actual code. It is created using Feb CTP of SQL Server 2008 (CTP 6).
CREATE TABLE source
(ID int NOT NULL,
FullName nvarchar(35))
CREATE TABLE target
(ID int NOT NULL,
FullName nvarchar(35))
INSERT INTO Source Values(1,'Madhuri Dixit')
INSERT INTO Source VALUES(2,'Aishwarya Roy')
INSERT INTO Source VALUES(3,'Bipasha Baasu')
INSERT INTO [target] VALUES(1,'Madhuri Nene')
INSERT INTO [target] VALUES(2,'Aishwarya Bachchan')
INSERT INTO [target] VALUES(4,'Kareena Kapoor')
With following statement only the update is done if there is match :
MERGE Source src
using (SELECT * FROM [target]) as t
ON src.Id = T.Id
WHEN MATCHED
THEN UPDATE SET src.FullName=t.FullName;
SELECT * FROM source
SELECT * FROM [target]
The following will be the actual values in both the tables
Now let’s try insert as well as update with Marge statement. Let’s delete previous records from the tables and continue with fresh data :
DELETE source
DELETE [target]
INSERT INTO Source Values(1,'Madhuri Dixit')
INSERT INTO Source VALUES(2,'Aishwarya Roy')
INSERT INTO Source VALUES(3,'Bipasha Baasu')
INSERT INTO [target] VALUES(1,'Madhuri Nene')
INSERT INTO [target] VALUES(4,'Kareena Kapoor')
INSERT INTO [target] VALUES(5,'Priyanka Chopra')
SELECT * FROM source
SELECT * FROM [target]
The select query result will look as follows :
After using Merge with Insert and Delete
MERGE Source src
using (SELECT * FROM [target]) as t
ON src.Id = T.Id
WHEN MATCHED
THEN UPDATE SET src.FullName=t.FullName
WHEN TARGET NOT MATCHED
THEN INSERT VALUES (t.ID, t.FullName);
SELECT * FROM source
SELECT * FROM [target]
The result of the Merge statement will give following output :
Now let’s try all three conditions and see the result. Let’s start fresh with the records again :
DELETE source
DELETE [target]
INSERT INTO Source Values(1,'Madhuri Dixit')
INSERT INTO Source VALUES(2,'Aishwarya Roy')
INSERT INTO Source VALUES(3,'Bipasha Baasu')
INSERT INTO [target] VALUES(1,'Madhuri Nene')
INSERT INTO [target] VALUES(4,'Kareena Kapoor')
INSERT INTO [target] VALUES(5,'Priyanka Chopra')
SELECT * FROM source
SELECT * FROM [target]
The result will be as follows :
Let’s use Merge and see the result
We no longer see record for ID =3 as it gets deleted with this merge statement.
Summary
Do not forget to give ; (semi colon) at the end of Merge statement. You will end up in parser error otherwise. If multiple rows match in target for criteria following error is given.
Msg 8672, Level 16, State 1, Line 1 The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
This is advantage of Merge over Update with join. Merge is deterministic whereas Update with join will update any row without any error.
Let’s see some advantages for using Merge statement.
With Merge statement the ability to insert a row if it does not exist or to update if it exists, is provided with a single statement. With previous version of SQL it was required to create separate statements for insert, update or delete to be done. With Merge statement, a source table (or a query) is allowed to join with target table (or updatable view) based on a criteria. Depending upon the match of the criteria specified update, insert or delete can be used. There are 3 options in the condition WHEN MATCHED, WHEN TARGET NOT MATCHED, WHEN SOURCE NOT MATCHED.
The syntax of Merge statement is as follows :
MERGE source
USING
(SELECT * FROM TARGET)
ON criteria
WHEN MATCHED
THEN UPDATE/DELETE
WHEN TARGET NOT MATCHED
THEN INSERT
WHEN SOURCE NOT MATCHED
THEN UPDATE/DELETE
Let’s see some actual code. It is created using Feb CTP of SQL Server 2008 (CTP 6).
CREATE TABLE source
(ID int NOT NULL,
FullName nvarchar(35))
CREATE TABLE target
(ID int NOT NULL,
FullName nvarchar(35))
INSERT INTO Source Values(1,'Madhuri Dixit')
INSERT INTO Source VALUES(2,'Aishwarya Roy')
INSERT INTO Source VALUES(3,'Bipasha Baasu')
INSERT INTO [target] VALUES(1,'Madhuri Nene')
INSERT INTO [target] VALUES(2,'Aishwarya Bachchan')
INSERT INTO [target] VALUES(4,'Kareena Kapoor')
With following statement only the update is done if there is match :
MERGE Source src
using (SELECT * FROM [target]) as t
ON src.Id = T.Id
WHEN MATCHED
THEN UPDATE SET src.FullName=t.FullName;
SELECT * FROM source
SELECT * FROM [target]
The following will be the actual values in both the tables
Now let’s try insert as well as update with Marge statement. Let’s delete previous records from the tables and continue with fresh data :
DELETE source
DELETE [target]
INSERT INTO Source Values(1,'Madhuri Dixit')
INSERT INTO Source VALUES(2,'Aishwarya Roy')
INSERT INTO Source VALUES(3,'Bipasha Baasu')
INSERT INTO [target] VALUES(1,'Madhuri Nene')
INSERT INTO [target] VALUES(4,'Kareena Kapoor')
INSERT INTO [target] VALUES(5,'Priyanka Chopra')
SELECT * FROM source
SELECT * FROM [target]
The select query result will look as follows :
After using Merge with Insert and Delete
MERGE Source src
using (SELECT * FROM [target]) as t
ON src.Id = T.Id
WHEN MATCHED
THEN UPDATE SET src.FullName=t.FullName
WHEN TARGET NOT MATCHED
THEN INSERT VALUES (t.ID, t.FullName);
SELECT * FROM source
SELECT * FROM [target]
The result of the Merge statement will give following output :
Now let’s try all three conditions and see the result. Let’s start fresh with the records again :
DELETE source
DELETE [target]
INSERT INTO Source Values(1,'Madhuri Dixit')
INSERT INTO Source VALUES(2,'Aishwarya Roy')
INSERT INTO Source VALUES(3,'Bipasha Baasu')
INSERT INTO [target] VALUES(1,'Madhuri Nene')
INSERT INTO [target] VALUES(4,'Kareena Kapoor')
INSERT INTO [target] VALUES(5,'Priyanka Chopra')
SELECT * FROM source
SELECT * FROM [target]
The result will be as follows :
Let’s use Merge and see the result
We no longer see record for ID =3 as it gets deleted with this merge statement.
Summary
Do not forget to give ; (semi colon) at the end of Merge statement. You will end up in parser error otherwise. If multiple rows match in target for criteria following error is given.
Msg 8672, Level 16, State 1, Line 1 The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
This is advantage of Merge over Update with join. Merge is deterministic whereas Update with join will update any row without any error.
Find First and Last Day of the Current Quarter in SQL Server
I was recently working on a requirement where the user wanted a report with data from the First day to the Last Day of the current Quarter
Here's a simple way to find the Find First and Last Day of the current quarter in SQL Server 2005/2008
SELECT DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0) as FirstDayOfQuarter
SELECT DATEADD(qq,DATEDIFF(qq,-1,GETDATE()),-1) as LastDayOfQuarter
OUTPUT
FirstDayOfQuarter LastDayOfQuarter
2009-04-01 00:00:00.000 2009-06-30 00:00:00
Here's a simple way to find the Find First and Last Day of the current quarter in SQL Server 2005/2008
SELECT DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0) as FirstDayOfQuarter
SELECT DATEADD(qq,DATEDIFF(qq,-1,GETDATE()),-1) as LastDayOfQuarter
OUTPUT
FirstDayOfQuarter LastDayOfQuarter
2009-04-01 00:00:00.000 2009-06-30 00:00:00
A Simple Family Tree Query Using Recursive CTE’s in SQL Server 2005/2008
I was recently working with one of my colleagues Steve on a Family Tree query. His software collected information about Relations of a person and then displayed it in a TreeView. We started discussing about Recursive Common Table Expressions(CTE) and how well CTE’s fit into such requirements. I will give you an example of how we used Recursive CTE’s to get the Parent and Generation Level of each person in the Family Tree. One of the tables in his software looked similar to the following -
Note: A Family Tree usually contains many more columns than the one shown here. I have reduced the columns of the original table for understanding purposes. We are also considering only one of the Parents (Father or Mother) of each person in this query
DECLARE @TT TABLE
(
ID int,
Relation varchar(25),
Name varchar(25),
ParentID int
)
INSERT @TT
SELECT 1,' Great GrandFather' , 'Thomas Bishop', null UNION ALL
SELECT 2,'Grand Mom', 'Elian Thomas Wilson' , 1 UNION ALL
SELECT 3, 'Dad', 'James Wilson',2 UNION ALL
SELECT 4, 'Uncle', 'Michael Wilson', 2 UNION ALL
SELECT 5, 'Aunt', 'Nancy Manor', 2 UNION ALL
SELECT 6, 'Grand Uncle', 'Michael Bishop', 1 UNION ALL
SELECT 7, 'Brother', 'David James Wilson',3 UNION ALL
SELECT 8, 'Sister', 'Michelle Clark', 3 UNION ALL
SELECT 9, 'Brother', 'Robert James Wilson', 3 UNION ALL
SELECT 10, 'Me', 'Steve James Wilson', 3
Here’s the query to find the Parent and Generation Level using Recursive CTE’s
;WITH FamilyTree
AS
(
SELECT *, CAST(NULL AS VARCHAR(25)) AS ParentName, 0 AS Generation
FROM @TT
WHERE ParentID IS NULL
UNION ALL
SELECT Fam.*,FamilyTree.Name AS ParentName, Generation + 1
FROM @TT AS Fam
INNER JOIN FamilyTree
ON Fam.ParentID = FamilyTree.ID
)
SELECT * FROM FamilyTree
Note: A Family Tree usually contains many more columns than the one shown here. I have reduced the columns of the original table for understanding purposes. We are also considering only one of the Parents (Father or Mother) of each person in this query
DECLARE @TT TABLE
(
ID int,
Relation varchar(25),
Name varchar(25),
ParentID int
)
INSERT @TT
SELECT 1,' Great GrandFather' , 'Thomas Bishop', null UNION ALL
SELECT 2,'Grand Mom', 'Elian Thomas Wilson' , 1 UNION ALL
SELECT 3, 'Dad', 'James Wilson',2 UNION ALL
SELECT 4, 'Uncle', 'Michael Wilson', 2 UNION ALL
SELECT 5, 'Aunt', 'Nancy Manor', 2 UNION ALL
SELECT 6, 'Grand Uncle', 'Michael Bishop', 1 UNION ALL
SELECT 7, 'Brother', 'David James Wilson',3 UNION ALL
SELECT 8, 'Sister', 'Michelle Clark', 3 UNION ALL
SELECT 9, 'Brother', 'Robert James Wilson', 3 UNION ALL
SELECT 10, 'Me', 'Steve James Wilson', 3
Here’s the query to find the Parent and Generation Level using Recursive CTE’s
;WITH FamilyTree
AS
(
SELECT *, CAST(NULL AS VARCHAR(25)) AS ParentName, 0 AS Generation
FROM @TT
WHERE ParentID IS NULL
UNION ALL
SELECT Fam.*,FamilyTree.Name AS ParentName, Generation + 1
FROM @TT AS Fam
INNER JOIN FamilyTree
ON Fam.ParentID = FamilyTree.ID
)
SELECT * FROM FamilyTree
Rename a Column in a Table in a SQL Server Database
This is a very simple script I am sharing today. A sqlservercurry.com user mailed me and asked me the simplest way to rename a column. Well here’s the query I have been using all along
USE Social;
GO
EXEC sp_rename 'dbo.Person.PersonName', 'PersonNm', 'COLUMN';
GO
This query renames the Column ‘PersonName’ in the Person table to ‘PersonNm’. Be careful while you do so, as you may affect Stored Procedures that use the column. Infact, when you run the query shown above, a caution gets displayed in your SQL Server Management Studio warning you of the change.
Caution: Changing any part of an object name could break scripts and stored procedures.
USE Social;
GO
EXEC sp_rename 'dbo.Person.PersonName', 'PersonNm', 'COLUMN';
GO
This query renames the Column ‘PersonName’ in the Person table to ‘PersonNm’. Be careful while you do so, as you may affect Stored Procedures that use the column. Infact, when you run the query shown above, a caution gets displayed in your SQL Server Management Studio warning you of the change.
Caution: Changing any part of an object name could break scripts and stored procedures.
Generate Insert Statements For a SQL Server Table
Microsoft SQL Server Database Publishing Wizard is a great tool to generate script for both schema and data for a database. However what if you were to write a script that could do that for you.
Here’s a real cool script written by johnnycrash that generates Insert Statements for a SQL Server Table.
For demonstration purposes, I am using the Culture table of the AdventureWorks database.
USE AdventureWorks
GO
DECLARE @Columns VARCHAR(max);
SET @Columns = '[CultureID], [Name], [ModifiedDate]'
DECLARE @Table VARCHAR(max);
SET @Table = 'Production.Culture'
DECLARE @SQL VARCHAR(max)
SET @SQL = 'DECLARE @S VARCHAR(MAX)
SELECT @S = ISNULL(@S + '' UNION '',
''INSERT INTO ' + @Table + '(' + @Columns + ')'')
+ CHAR(13) + CHAR(10)
+ ''SELECT '' + ' + REPLACE(REPLACE(REPLACE(@Columns, ',', ' + '', '' + '),
'[', ''''''''' + CAST('),']',' AS VARCHAR(max)) + ''''''''')
+' FROM ' + @Table
+ ' PRINT @S'
EXEC (@SQL)
Here’s a real cool script written by johnnycrash that generates Insert Statements for a SQL Server Table.
For demonstration purposes, I am using the Culture table of the AdventureWorks database.
USE AdventureWorks
GO
DECLARE @Columns VARCHAR(max);
SET @Columns = '[CultureID], [Name], [ModifiedDate]'
DECLARE @Table VARCHAR(max);
SET @Table = 'Production.Culture'
DECLARE @SQL VARCHAR(max)
SET @SQL = 'DECLARE @S VARCHAR(MAX)
SELECT @S = ISNULL(@S + '' UNION '',
''INSERT INTO ' + @Table + '(' + @Columns + ')'')
+ CHAR(13) + CHAR(10)
+ ''SELECT '' + ' + REPLACE(REPLACE(REPLACE(@Columns, ',', ' + '', '' + '),
'[', ''''''''' + CAST('),']',' AS VARCHAR(max)) + ''''''''')
+' FROM ' + @Table
+ ' PRINT @S'
EXEC (@SQL)
Site to follow
http://www.sqlservercurry.com/search/label/Analysis%20Services%20SSAS?updated-max=2009-08-15T04:55:00-07:00&max-results=20----SSAS
http://www.sqlservercurry.com/search/label/Transact%20SQL%20T-SQL?updated-max=2010-01-08T04:55:00-08:00&max-results=20---SQL Server2008
http://www.sqlservercurry.com/search/label/Reporting%20Services%20SSRS----SSRS
http://www.sqlservercurry.com/search/label/Transact%20SQL%20T-SQL?updated-max=2010-01-08T04:55:00-08:00&max-results=20---SQL Server2008
http://www.sqlservercurry.com/search/label/Reporting%20Services%20SSRS----SSRS
Different ways for executing SQL Server Integration Package
With Business Intelligence Development Studio (BIDs) we get a template for creating SQL Server Integration Services (SSIS) Project, which in turn creates individual packages. Each package has an extension .dtsx (Data Transformation Services with XML) which is a unit of deployment and execution. There are several ways in which the package can be executed. Here are some of those
1. Run the package from BIDs itself.
With this option, we have the advantage of working with debug mode for control flow tasks and Data Viewer for Data Flow Tasks.
The disadvantage is that we will not have Visual Studio on deployment server.
2. Run the package with Command line utility DTEXECUI
This means Data Transfer EXecution User Interface
User Interface is provided with the help of which certain properties like setting command line parameters, specifying configuration files, setting values for variables at run time etc
3. Run the package wit Command line utility DTEXEC
As the name suggests, this option does not have an user interface but certain switches that can be used with the command
Depending upon where the package is stored, we can use different switches for file deployment or SQL server deployment
Users can specify properties values with /Set
4. Run the package after deploying it on the server either in a File System or on a SQL server database (MSDB)
This option will provide a similar interface like DTEXECUI
5. Run the package as a SQL Server Agent job
Create a step for SQL server agent job to execute a package and depending upon the schedule, the package will be executed automatically
6. Write a code with Visual Studio with C# or VB.NET which will execute package from the application. In this option, we will need to first set references to System.SQLServer.ManagedDTS so as to load existing package in the application by using Application.LoadPackage() method and then execute it by using the Package.Execute() method.
C#
using Microsoft.SqlServer.Dts.Runtime;
Package pkg = new Package();
Application app = new Application();
//load and execute package
pkg = app.LoadPackage(@"/.dtsx”, null);
pkg.Execute();
VB.NET
Imports Microsoft.SqlServer.Dts.Runtime
Package pkg= new Packagr()
Application app= new Application()
'load and package
pkg = app.LoadPackage(“/.dtsx”,null)
pkg.Execute()
1. Run the package from BIDs itself.
With this option, we have the advantage of working with debug mode for control flow tasks and Data Viewer for Data Flow Tasks.
The disadvantage is that we will not have Visual Studio on deployment server.
2. Run the package with Command line utility DTEXECUI
This means Data Transfer EXecution User Interface
User Interface is provided with the help of which certain properties like setting command line parameters, specifying configuration files, setting values for variables at run time etc
3. Run the package wit Command line utility DTEXEC
As the name suggests, this option does not have an user interface but certain switches that can be used with the command
Depending upon where the package is stored, we can use different switches for file deployment or SQL server deployment
Users can specify properties values with /Set
4. Run the package after deploying it on the server either in a File System or on a SQL server database (MSDB)
This option will provide a similar interface like DTEXECUI
5. Run the package as a SQL Server Agent job
Create a step for SQL server agent job to execute a package and depending upon the schedule, the package will be executed automatically
6. Write a code with Visual Studio with C# or VB.NET which will execute package from the application. In this option, we will need to first set references to System.SQLServer.ManagedDTS so as to load existing package in the application by using Application.LoadPackage() method and then execute it by using the Package.Execute() method.
C#
using Microsoft.SqlServer.Dts.Runtime;
Package pkg = new Package();
Application app = new Application();
//load and execute package
pkg = app.LoadPackage(@"
pkg.Execute();
VB.NET
Imports Microsoft.SqlServer.Dts.Runtime
Package pkg= new Packagr()
Application app= new Application()
'load and package
pkg = app.LoadPackage(“
pkg.Execute()
Find Primary Key of a SQL Server Table
Here’s a quick way to find the primary key of a SQL Server 2005/2008 Table using TABLE_CONSTRAINTS
USE Northwind
go
SELECT ISKC.TABLE_SCHEMA, ISKC.TABLE_NAME, ISKC.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS ISTC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ISKC
ON ISTC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
ISTC.CONSTRAINT_NAME = ISKC.CONSTRAINT_NAME
WHERE ISKC.TABLE_NAME = 'Employees'
ORDER BY ISKC.TABLE_NAME, ISKC.ORDINAL_POSITION
USE Northwind
go
SELECT ISKC.TABLE_SCHEMA, ISKC.TABLE_NAME, ISKC.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS ISTC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ISKC
ON ISTC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
ISTC.CONSTRAINT_NAME = ISKC.CONSTRAINT_NAME
WHERE ISKC.TABLE_NAME = 'Employees'
ORDER BY ISKC.TABLE_NAME, ISKC.ORDINAL_POSITION
Subscribe to:
Posts (Atom)