http://www.mssqltips.com/sqlservertip/2999/different-ways-to-find-sql-server-object-dependencies/
Find All Queries Run Against a Table
This script will show you all the queries that have run against a particular table since the last time SQL Server was rebooted. This query is good in helping
to define indexes. This only works against SQL 2008.
SELECT DISTINCT TOP 1000
ProcedureName = OBJECT_SCHEMA_NAME(qt.objectid) + '.' + OBJECT_NAME(qt.objectid)
,SQLStatement = SUBSTRING(
qt.Text
,(qs.statement_start_offset/2)+1
,CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - (qs.statement_start_offset/2) + 1
)
,DiskReads = qs.total_physical_reads -- The worst reads, disk reads
,MemoryReads = qs.total_logical_reads --Logical Reads are memory reads
,ExecutionCount = qs.execution_count
,CPUTime = qs.total_worker_time
,DiskWaitAndCPUTime = qs.total_elapsed_time
,MemoryWrites = qs.max_logical_writes
,DateCached = qs.creation_time
,DatabaseName = DB_Name(qt.dbid)
,LastExecutionTime = qs.last_execution_time
--,sre.*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_sql_referenced_entities(
OBJECT_SCHEMA_NAME(qt.objectid) + '.' + OBJECT_NAME(qt.objectid)
, 'OBJECT'
) sre
WHERE qt.dbid = db_id() -- Filter by current database
AND sre.referenced_schema_name + '.' + sre.referenced_entity_name = 'dbo.Table'
Find all the queries executed recently on a database
Columns:
SELECT object_name(object_id) AS 'TableName', [Name] AS 'ColumnName', Column_ID
FROM sys.all_columns where name like '%Currency%'
ORDER BY TableName, Column_ID
Constraints
SELECT object_name(o.parent_object_id) AS 'ParentObject', s.name AS 'Schema', o.Name AS 'PrimaryKey'
FROM sys.objects o
INNER JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE o.Type IN ('C', 'D', 'UQ')
ORDER BY o.Name;
GO
SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure
Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always
return accurate results.
----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'
----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'
--‘%s parameter not valid for address type %s’, @pQtyPrint, @ptAddresstype'
--when @ptActiontype='CT' then @ptTelex
-- when @ptActiontype in ('CB','CE','CP') then @ptEmail
-- when @ptActiontype in ('CN','CC') then @ptAddressline1,@pQtyPrint,@pDeliveryMethod,@pBuyDestID,@pSellDestID
-- when @ptActiontype='CX' then @pFileExportID
--end
--select case when @ptActiontype='CF' then @ptAttention,@ptFax
-- when @ptActiontype='CT' then @ptTelex
-- when @ptActiontype in ('CB','CE','CP') then @ptEmail
-- when @ptActiontype in ('CN','CC') then @ptAddressline1,@pQtyPrint,@pDeliveryMethod,@pBuyDestID,@pSellDestID
-- when @ptActiontype='CX' then @pFileExportID
Retrieving Column Names of a Table in SQL Server 2012
Information_ Schema
Information schema views provide an internal view of the SQL Server metadata independent of the system table. You can use the select statement with the
Information Schema to retrieve a table's columns from the news object or table. The following query will give the table's column names:
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'News'
question:How to change a stored proc's parameter datatype - without editing a CREATE PROC script
Let's suppose I inherit a database with 1000 stored procedures, most of which have a SMALLINT parameter named @CustomerID.
Now lets suppose our CustomerID column has exceeded the capacity of a SMALLINT and we need to convert it to an INT.
Modifying the columns in the related tables is easy enough- I can fire off a query against sys.coulmns that generates a series of ALTER TABLE statements
which alter the CustomerID column to an INT.
But my stumbling block seems to be how I can modify the @CustomerID parameter for many stored procedures. I 'd really like to avoid having to script out all
the procs and manually edit each one. I hope I am missing an obvious easy/elegant solution!
ans:It sounds like you've got something working with SMO, but as a side note for anyone interested, the following will list all parameters of datatype
'smallint' for all user created stored procedures.
select schema_name(o.schema_id) schema_name, o.name obj_name, p.name param_name, t.name param_type
from sys.all_parameters p
join systypes t on t.xusertype = p.user_type_id
join sys.objects o on o.object_id = p.object_id
where o.type = 'P' -- type procedure
and t.name = 'smallint' -- data type
and o.is_ms_shipped = 0 -- user created
order by
schema_name(o.schema_id),
o.name;
question:data type size increase in one table,so we need to find all stored procedure which are using that data type?
select distinct id,name from sys.syscomments a,sys.all_objects b where a.id=b.object_id and text like '%KeyType%char%'
nested stored procedure dependencies
DECLARE @DepTree TABLE (ObjNum int identity(1,1) not null, Name varchar(1000), DependsOn varchar(1000), ObjectType char(2), DepLevel smallint)
INSERT INTO @DepTree (Name, DependsOn, ObjectType, DepLevel)
SELECT DependsOn = S.Name, S.Name, ObjectType = S.XType, DepLevel = 0
FROM sys.sysobjects S
WHERE S.Name = 'pEqAccLookup' and S.xtype='P'
DECLARE @Name varchar(1000)
DECLARE @DependsOn varchar(1000)
DECLARE @DepLevel smallint
DECLARE @ObjNum int
SET @ObjNum = 1
WHILE EXISTS(SELECT 1 FROM @DepTree WHERE ObjNum = @ObjNum )
BEGIN
SELECT @Name = Name, @DependsOn = DependsOn, @DepLevel = DepLevel FROM @DepTree WHERE ObjNum = @ObjNum
-- this block finds objects that the current object of interest depends on (moving _down_ the dependency chain):
IF @DepLevel >= 0
INSERT INTO @DepTree (Name, DependsOn, ObjectType, DepLevel)
SELECT DISTINCT S1.Name, DependsOn = S2.Name, ObjectType = S2.XType, DepLevel = @DepLevel + 1
FROM sys.sysdepends DP
JOIN sys.sysobjects S1 ON S1.ID = DP.ID and S1.xtype='P'
JOIN sys.sysobjects S2 ON S2.ID = DP.DepID and S2.xtype='P'
WHERE S1.Name = @DependsOn
ORDER BY 1, 3, 2
-- this block finds objects that depend on the current object of interest (moving _up_ the dependency chain):
IF @DepLevel <= 0
INSERT INTO @DepTree (Name, DependsOn, ObjectType, DepLevel)
SELECT DISTINCT S2.Name, DependsOn = S1.Name, ObjectType = S2.XType, DepLevel = @DepLevel - 1
FROM sys.sysdepends DP
JOIN sys.sysobjects S1 ON S1.ID = DP.DepID and S1.xtype='P'
JOIN sys.sysobjects S2 ON S2.ID = DP.ID and S2.xtype='P'
WHERE S1.Name = @Name
ORDER BY 1, 3, 2
SET @ObjNum = @ObjNum + 1
END
SELECT * FROM @DepTree
================================================================================================================
--coalesce is used for non-null for calculation wages(totalsal based on hourly_wage, salary, and commission) and also used for one column data(all rows)
,"shown as a full string(as one row)
=====================================================================================================================
Sql Server: Convert table Column data into comma separated string or row
SQL SERVER – Create a Comma Delimited/Seprated string or row Using SELECT Clause From Table Column/fields
DECLARE @test NVARCHAR(max)
SELECT @test = COALESCE(@test + ',', '') + firstname FROM hr.Employees
SELECT firstname = @test
Source:http://oops-solution.blogspot.in/2011/11/sql-server-convert-table-column-data.html
=================================================================================================
--In the following example, the wages table includes three columns that contain information about the yearly wages of the employees:
--the hourly wage, salary, and commission. However, an employee receives only one type of pay.
--To determine the total amount paid to all employees, use COALESCE to receive only the nonnull value found in hourly_wage, salary, and commission.
SELECT emp_id,CAST(COALESCE(hourly_wage * 40 * 52,
salary,
commission * num_sales) AS money) AS 'Total Salary'
FROM dbo.wages
ORDER BY 'Total Salary';
GO
======================================
comparing COALESCE and ISNULL
The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.
1.
Because ISNULL is a function, it is evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times.
2.
Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression
rules and returns the data type of value with the highest precedence.
3.
The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return
value is a non-nullable one) whereas COALESCE with non-null parameters is considered to be NULL. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1)
although equivalent have different nullability values. This makes a difference if you are using these expressions in computed columns, creating key
constraints or making the return value of a scalar UDF deterministic so that it can be indexed as shown in the following example
CREATE TABLE #Demo
(
col1 integer NULL,
col2 AS COALESCE(col1, 0) PRIMARY KEY,
col3 AS ISNULL(col1, 0)
);
-- This statement succeeds because the nullability of the
-- ISNULL function evaluates AS NOT NULL.
CREATE TABLE #Demo
(
col1 integer NULL,
col2 AS COALESCE(col1, 0),
col3 AS ISNULL(col1, 0) PRIMARY KEY
);
4.
Validations for ISNULL and COALESCE are also different. For example, a NULL value for ISNULL is converted to int whereas for COALESCE, you must provide a
data type.
5.
ISNULL takes only 2 parameters whereas COALESCE takes a variable number of parameters.
Note:for checking;create table using primary key
nullability of the ISNULL function evaluates AS NOT NULL.
nullability of the COALESCE expression for col2 evaluates to NULL.
==========================================
UPDATE GenderSET SET sex = CASE sex WHEN 'm' THEN 'f' ELSE 'm' END
2.create table Emptst ( Id int, Name varchar(50), DeptId int)
create table Depttst ( Id int, Name varchar(50))
select * from Emptst,Depttst
results is
--The query will result in 50 rows as a “cartesian product” or “cross join”, which is the default whenever the ‘where’ clause is omitted.
3.select case when null = null then 'Yup' else 'Nope' end as Result;
result:
NOPE
4.Here is a query written to return the list of customers not referred by Jane Smith:
SELECT Name FROM Customers WHERE ReferredBy <> 2;
ans:
SELECT Name FROM Customers WHERE ReferredBy = NULL OR ReferredBy <> 2
============================================================================================
SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup
--without use any index -- Table scan
--with cluster index -- now table scan converted to clustered index scan
--with non-clustered index(Where clause) -- then key lookup is happened on execution plan
--Here problem is where clause is retreive from index and select columns data is retrieve from table scan(data page),so performance is de-greated.
--Key lookup ,it should be removed from the execution plan to improve performance
--To remove that key lookup-->2 methods:one is cover index(ie;select columns +where columns(Non clutered index),included columns(create non-clustered index)
--Best method is included columns(Creating an included column non-clustered index)
-- I have mentioned in my previous article that I prefer the Method 2. The reason is that method 2 has many advantages over method 1.
--1) Index can exceed the 900-byte limitation of the index key.
--2) Index can include datatypes that are not allowed as key columns – varchar(max), nvarchar(max) or XML.
--3) Size of the key index can be reduced, which improves the overall performance of the index operation.
CREATE NONCLUSTERED INDEX [IX_OneIndex_Include] ON [dbo].[OneIndex]
(
City
) INCLUDE (FirstName,ID) ON [PRIMARY]
GO
Here ID is clustered index and where clause City is also non-clustered index
In earlier article I have suggested to include all columns but in reality in any non clustered index there is no need to include columns included in
clustered index.
--All non clusteredindex automatically contains pointers to clustered index any way.
IE;CREATE NONCLUSTERED INDEX [IX_OneIndex_Include3] ON [dbo].[OneIndex]
(
City
) INCLUDE (FirstName) ON [PRIMARY]
GO
===================================
SELECT ID, FirstName
FROM OneIndex WITH (INDEX(IX_OneIndex_Include3))
WHERE City = 'Las Vegas'
GO
--Let us examine the execution plan and compare the query costs and also verify that if both the index usages are forcing index seek instead of index scan.
=============================================================================================================================
What is covering index?
It is an index that can satisfy a query just by its index keys without having needed to touch the data pages.
It means that when a query is fired, SQL Server doesn’t need to go to the table to retrieve the rows, but can produce the results directly from the index as
the index covers all the columns used in query.
Well, summary is very simple that because of cover index, the performance of the query improves and IO decreases. Included index is another good concept, we
will talk about it in later post.
Before Cover Index: IO Cost 0.006088
logical reads 6, physical reads 3, read-ahead reads 16,
After Cover Index: IO Cost 0.0038657
logical reads 3, physical reads 1, read-ahead reads 0
=====================================================================
--Table spool:stores data from i/p to temporary table in order to optimize rewinds
--Index spool:re-format from i/p to temporary index,which is then used for seeking with the supplied seek predicate
--clustered index seek:scannin particular ranges of rows from clustered index
--Assert:used to verify that specificied condition exists
==========================================================
T-SQL: Simplified CASE expression
1.ISNULL
2.COALESCE
3.IIF
4.CHOOSE
============================================
-- Customers with orders handled by all employees from the USA
-- using literals
SELECT custid
FROM Sales.Orders
WHERE empid IN(1, 2, 3, 4, 8)
GROUP BY custid
HAVING COUNT(DISTINCT empid) = 5;
============
Orders with maximum orderdate for each employee
ans:correlated subquery(max(date) and empid join condition)
-- Comparing Current to Previous Year’s Number of Customers
use multi reference(left join)
===================================
The fact of the matter is that computer systems have always had the GIGO (garbage in garbage out) problem.
the SQL server needed to be restarted. The tables are initially created in the model database and then copied to tempdb after the first restart of SQL
Server. This is one-off process as we built a new server.
----------------------------------------------------------------------------------------------------------------
Change data capture in sql server
exec sys.sp_cdc_enable_db--for enabling CDC on dbase
--For CDC enabled on table
EXECUTE sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'MyOrders',
@capture_instance = N'InsideTSQL2008';
-- =====================================================
-- Determine Whether a Table is Enabled for CDC Template
-- =====================================================
SELECT is_tracked_by_cdc ,*FROM sys.tables
--if u do any operations like Delete,insert,update
--once enabled cdc table will track all the data(by creating one table ie;schemanametablename_CT),it geather last value and updated value if u do update
operation
-----------------------------------------------------------------------------------
there one primaryu key column in a table 6:13 PM
i wnat to create clustered index on that 6:14 PM
*i wnat to create non clustered index on that 6:14 PM
instead of clustered index 6:14 PM
ans: drop the primary key and then only clustered index can be dropped 6:14 PM
then create primary along with non clustered index
Ans:
create table tpk(id int primary key,name varchar(40))
ALTER TABLE [dbo].[tpk] DROP CONSTRAINT PK__tpk__3213E83F3864608B
ALTER TABLE [dbo].[tpk] ADD PRIMARY KEY nonCLUSTERED
(
[id] ASC
)
----------
-- Row Number with Arbitrary Order
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS orderid,
orderdate, custid, empid
FROM sales.Orders;
----------------------------------
--CHECK Constraint
---------------------------------------------------------------------
USE AdventureWorks2008;
GO
SELECT DISTINCT Status FROM Sales.SalesOrderHeader;
--Result: 5 (the status 5 is set for all orders)
--Let's create a CHECK constraint, which disallows values greather than 5 for the status column
ALTER TABLE Sales.SalesOrderHeader WITH CHECK ADD CONSTRAINT chkStatus CHECK (Status < 6);
--Check for rows with the status 4 or 6
SELECT * FROM Sales.SalesOrderHeader WHERE Status = 4;
SELECT * FROM Sales.SalesOrderHeader WHERE Status = 6;
--Result: 100% : 0% (in the first case Clustered Index Scan has been performed, for the second query
-- the table was not touched at all!)
--Clean-Up
ALTER TABLE Sales.SalesOrderHeader DROP CONSTRAINT chkStatus;
-------------------------------------
---------------------------------------------------------------------
--Data Type Conversions
---------------------------------------------------------------------
-- 3.1 nvarchar column and varchar argument - NOTHING happens
--VARCHAR column and NVARCHAR argument - CONVERSION needed
--Result: 5% : 95% (Conversion overhead is significant; non-unicode will be converted to Unicode)
--Logical Reads: 5 vs. 54
--Solution: Use the column data type for argument too, or explicitely convert the argument data type to the column data type
SELECT FirstName, LastName_Varchar FROM dbo.Contacts WHERE LastName_Varchar ='Atkinson';
SELECT FirstName, LastName_Varchar FROM dbo.Contacts WHERE LastName_Varchar = CONVERT(VARCHAR(50),N'Atkinson')
--Constraints and Performance
--Let's create a CHECK constraint, which disallows values greather than 5 for the status column
ALTER TABLE Sales.SalesOrderHeader WITH CHECK ADD CONSTRAINT chkStatus CHECK (Status < 6);
WHERE Status = 4;
WHERE Status = 6;
--Result: 100% : 0% (in the first case Clustered Index Scan has been performed, for the second query
-- the table was not touched at all!)
---------------------------------------------------------------------
--NON-SARG in the WHERE clause
--IN vs. BETWEEN
--Result: Query 1: 2 rows, 6 logical reads
-- Query 2: 2 rows, 3 logical reads (the same plan, but 2 scans for the query 1)
--Use Index for Aggregate Functions
SELECT MAX(TotalDue) FROM Sales.SalesOrderHeader;
--Result: Clustered Index Scan (1406 log. reads)
--Create an index on the TotalDue and try again
IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE name='IX901')
CREATE NONCLUSTERED INDEX IX901 ON Sales.SalesOrderHeader (TotalDue);
GO
SELECT MAX(TotalDue) FROM Sales.SalesOrderHeader;
--Result: Index IX_SalesOrderHeader_TotalDue Scan (2 log. reads)
--Conculsion: Select columns can be also index candidates if they are part of aggregate functions
--verify existance
--Result: 50% : 50% (SQL Server does not count the rows - it stops when the first has been found)
--But if you use local variable to hold COUNT(*), of course it will count the rows
IF ((SELECT COUNT(*)
-- =============================================
-- Add another Sql Server as linked server
-- =============================================
sp_addlinkedserver N'<server_name, sysname, server1>', N'SQL Server'
GO
-- Drop linked server
-- =============================================
sp_dropserver @server = N'<server_name, sysname, server1>',
@droplogins = <option, char(10), NULL>
-- Configure linked server
-- =============================================
sp_serveroption @server = N'<server_name, sysname, server1>',
@optname = '<option_name, varchar(35), dist>',
@optvalue = N'<option_value, nvarchar(128), OFF>'
-- =============================================
-- List linked server
-- =============================================
sp_linkedservers
GO
-- Declare and using a KEYSET cursor
-- Declare and using a READ_ONLY cursor
--Declare and using an UPDATE cursor
-- Declare a SCROLL cursor and use various FETCH options
DECLARE <cursor_name, sysname, test_cursor> SCROLL CURSOR FOR
<select_statement, , SELECT * FROM pubs.dbo.authors>
OPEN <cursor_name, sysname, test_cursor>
-- Fetch " " in the cursor.
FETCH FIRST FROM <cursor_name, sysname, test_cursor>
--FETCH First,last,prior,next,absoute(rownum),relative(rownum) from cursorname
CLOSE <cursor_name, sysname, test_cursor>
DEALLOCATE <cursor_name, sysname, test_cursor>
---dbase
--==================================
-- Take database offline template
--==================================
ALTER DATABASE <Database_Name, sysname, Database_Name>
SET OFFLINE
--Drop dbase
DROP DATABASE <Database_Name, sysname, Database_Name>
--=====================================
-- Detach database template
--=====================================
EXEC sp_detach_db @dbname = '<database_name, sysname, your_database_name>'
,@skipchecks = 'true'
,@KeepFulltextIndexFile = 'true'
--==================================
-- Bring database online template
--==================================
ALTER DATABASE <Database_Name, sysname, Database_Name>
SET ONLINE
GO
--=====================================
-- Attach database template
--=====================================
IF NOT EXISTS(
SELECT *
FROM sys.databases
WHERE name = N'<database_name, sysname, your_database_name>'
)
CREATE DATABASE <database_name, sysname, your_database_name>
ON PRIMARY (FILENAME = '<database_primary_file_path,,C:\Program files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data
\your_database_name.MDF>')
FOR ATTACH
GO
Find All Queries Run Against a Table
This script will show you all the queries that have run against a particular table since the last time SQL Server was rebooted. This query is good in helping
to define indexes. This only works against SQL 2008.
SELECT DISTINCT TOP 1000
ProcedureName = OBJECT_SCHEMA_NAME(qt.objectid) + '.' + OBJECT_NAME(qt.objectid)
,SQLStatement = SUBSTRING(
qt.Text
,(qs.statement_start_offset/2)+1
,CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - (qs.statement_start_offset/2) + 1
)
,DiskReads = qs.total_physical_reads -- The worst reads, disk reads
,MemoryReads = qs.total_logical_reads --Logical Reads are memory reads
,ExecutionCount = qs.execution_count
,CPUTime = qs.total_worker_time
,DiskWaitAndCPUTime = qs.total_elapsed_time
,MemoryWrites = qs.max_logical_writes
,DateCached = qs.creation_time
,DatabaseName = DB_Name(qt.dbid)
,LastExecutionTime = qs.last_execution_time
--,sre.*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_sql_referenced_entities(
OBJECT_SCHEMA_NAME(qt.objectid) + '.' + OBJECT_NAME(qt.objectid)
, 'OBJECT'
) sre
WHERE qt.dbid = db_id() -- Filter by current database
AND sre.referenced_schema_name + '.' + sre.referenced_entity_name = 'dbo.Table'
Find all the queries executed recently on a database
Columns:
SELECT object_name(object_id) AS 'TableName', [Name] AS 'ColumnName', Column_ID
FROM sys.all_columns where name like '%Currency%'
ORDER BY TableName, Column_ID
Constraints
SELECT object_name(o.parent_object_id) AS 'ParentObject', s.name AS 'Schema', o.Name AS 'PrimaryKey'
FROM sys.objects o
INNER JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE o.Type IN ('C', 'D', 'UQ')
ORDER BY o.Name;
GO
SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure
Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always
return accurate results.
----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'
----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'
--‘%s parameter not valid for address type %s’, @pQtyPrint, @ptAddresstype'
--when @ptActiontype='CT' then @ptTelex
-- when @ptActiontype in ('CB','CE','CP') then @ptEmail
-- when @ptActiontype in ('CN','CC') then @ptAddressline1,@pQtyPrint,@pDeliveryMethod,@pBuyDestID,@pSellDestID
-- when @ptActiontype='CX' then @pFileExportID
--end
--select case when @ptActiontype='CF' then @ptAttention,@ptFax
-- when @ptActiontype='CT' then @ptTelex
-- when @ptActiontype in ('CB','CE','CP') then @ptEmail
-- when @ptActiontype in ('CN','CC') then @ptAddressline1,@pQtyPrint,@pDeliveryMethod,@pBuyDestID,@pSellDestID
-- when @ptActiontype='CX' then @pFileExportID
Retrieving Column Names of a Table in SQL Server 2012
Information_ Schema
Information schema views provide an internal view of the SQL Server metadata independent of the system table. You can use the select statement with the
Information Schema to retrieve a table's columns from the news object or table. The following query will give the table's column names:
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'News'
question:How to change a stored proc's parameter datatype - without editing a CREATE PROC script
Let's suppose I inherit a database with 1000 stored procedures, most of which have a SMALLINT parameter named @CustomerID.
Now lets suppose our CustomerID column has exceeded the capacity of a SMALLINT and we need to convert it to an INT.
Modifying the columns in the related tables is easy enough- I can fire off a query against sys.coulmns that generates a series of ALTER TABLE statements
which alter the CustomerID column to an INT.
But my stumbling block seems to be how I can modify the @CustomerID parameter for many stored procedures. I 'd really like to avoid having to script out all
the procs and manually edit each one. I hope I am missing an obvious easy/elegant solution!
ans:It sounds like you've got something working with SMO, but as a side note for anyone interested, the following will list all parameters of datatype
'smallint' for all user created stored procedures.
select schema_name(o.schema_id) schema_name, o.name obj_name, p.name param_name, t.name param_type
from sys.all_parameters p
join systypes t on t.xusertype = p.user_type_id
join sys.objects o on o.object_id = p.object_id
where o.type = 'P' -- type procedure
and t.name = 'smallint' -- data type
and o.is_ms_shipped = 0 -- user created
order by
schema_name(o.schema_id),
o.name;
question:data type size increase in one table,so we need to find all stored procedure which are using that data type?
select distinct id,name from sys.syscomments a,sys.all_objects b where a.id=b.object_id and text like '%KeyType%char%'
nested stored procedure dependencies
DECLARE @DepTree TABLE (ObjNum int identity(1,1) not null, Name varchar(1000), DependsOn varchar(1000), ObjectType char(2), DepLevel smallint)
INSERT INTO @DepTree (Name, DependsOn, ObjectType, DepLevel)
SELECT DependsOn = S.Name, S.Name, ObjectType = S.XType, DepLevel = 0
FROM sys.sysobjects S
WHERE S.Name = 'pEqAccLookup' and S.xtype='P'
DECLARE @Name varchar(1000)
DECLARE @DependsOn varchar(1000)
DECLARE @DepLevel smallint
DECLARE @ObjNum int
SET @ObjNum = 1
WHILE EXISTS(SELECT 1 FROM @DepTree WHERE ObjNum = @ObjNum )
BEGIN
SELECT @Name = Name, @DependsOn = DependsOn, @DepLevel = DepLevel FROM @DepTree WHERE ObjNum = @ObjNum
-- this block finds objects that the current object of interest depends on (moving _down_ the dependency chain):
IF @DepLevel >= 0
INSERT INTO @DepTree (Name, DependsOn, ObjectType, DepLevel)
SELECT DISTINCT S1.Name, DependsOn = S2.Name, ObjectType = S2.XType, DepLevel = @DepLevel + 1
FROM sys.sysdepends DP
JOIN sys.sysobjects S1 ON S1.ID = DP.ID and S1.xtype='P'
JOIN sys.sysobjects S2 ON S2.ID = DP.DepID and S2.xtype='P'
WHERE S1.Name = @DependsOn
ORDER BY 1, 3, 2
-- this block finds objects that depend on the current object of interest (moving _up_ the dependency chain):
IF @DepLevel <= 0
INSERT INTO @DepTree (Name, DependsOn, ObjectType, DepLevel)
SELECT DISTINCT S2.Name, DependsOn = S1.Name, ObjectType = S2.XType, DepLevel = @DepLevel - 1
FROM sys.sysdepends DP
JOIN sys.sysobjects S1 ON S1.ID = DP.DepID and S1.xtype='P'
JOIN sys.sysobjects S2 ON S2.ID = DP.ID and S2.xtype='P'
WHERE S1.Name = @Name
ORDER BY 1, 3, 2
SET @ObjNum = @ObjNum + 1
END
SELECT * FROM @DepTree
================================================================================================================
--coalesce is used for non-null for calculation wages(totalsal based on hourly_wage, salary, and commission) and also used for one column data(all rows)
,"shown as a full string(as one row)
=====================================================================================================================
Sql Server: Convert table Column data into comma separated string or row
SQL SERVER – Create a Comma Delimited/Seprated string or row Using SELECT Clause From Table Column/fields
DECLARE @test NVARCHAR(max)
SELECT @test = COALESCE(@test + ',', '') + firstname FROM hr.Employees
SELECT firstname = @test
Source:http://oops-solution.blogspot.in/2011/11/sql-server-convert-table-column-data.html
=================================================================================================
--In the following example, the wages table includes three columns that contain information about the yearly wages of the employees:
--the hourly wage, salary, and commission. However, an employee receives only one type of pay.
--To determine the total amount paid to all employees, use COALESCE to receive only the nonnull value found in hourly_wage, salary, and commission.
SELECT emp_id,CAST(COALESCE(hourly_wage * 40 * 52,
salary,
commission * num_sales) AS money) AS 'Total Salary'
FROM dbo.wages
ORDER BY 'Total Salary';
GO
======================================
comparing COALESCE and ISNULL
The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.
1.
Because ISNULL is a function, it is evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times.
2.
Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression
rules and returns the data type of value with the highest precedence.
3.
The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return
value is a non-nullable one) whereas COALESCE with non-null parameters is considered to be NULL. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1)
although equivalent have different nullability values. This makes a difference if you are using these expressions in computed columns, creating key
constraints or making the return value of a scalar UDF deterministic so that it can be indexed as shown in the following example
CREATE TABLE #Demo
(
col1 integer NULL,
col2 AS COALESCE(col1, 0) PRIMARY KEY,
col3 AS ISNULL(col1, 0)
);
-- This statement succeeds because the nullability of the
-- ISNULL function evaluates AS NOT NULL.
CREATE TABLE #Demo
(
col1 integer NULL,
col2 AS COALESCE(col1, 0),
col3 AS ISNULL(col1, 0) PRIMARY KEY
);
4.
Validations for ISNULL and COALESCE are also different. For example, a NULL value for ISNULL is converted to int whereas for COALESCE, you must provide a
data type.
5.
ISNULL takes only 2 parameters whereas COALESCE takes a variable number of parameters.
Note:for checking;create table using primary key
nullability of the ISNULL function evaluates AS NOT NULL.
nullability of the COALESCE expression for col2 evaluates to NULL.
==========================================
UPDATE GenderSET SET sex = CASE sex WHEN 'm' THEN 'f' ELSE 'm' END
2.create table Emptst ( Id int, Name varchar(50), DeptId int)
create table Depttst ( Id int, Name varchar(50))
select * from Emptst,Depttst
results is
--The query will result in 50 rows as a “cartesian product” or “cross join”, which is the default whenever the ‘where’ clause is omitted.
3.select case when null = null then 'Yup' else 'Nope' end as Result;
result:
NOPE
4.Here is a query written to return the list of customers not referred by Jane Smith:
SELECT Name FROM Customers WHERE ReferredBy <> 2;
ans:
SELECT Name FROM Customers WHERE ReferredBy = NULL OR ReferredBy <> 2
============================================================================================
SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup
--without use any index -- Table scan
--with cluster index -- now table scan converted to clustered index scan
--with non-clustered index(Where clause) -- then key lookup is happened on execution plan
--Here problem is where clause is retreive from index and select columns data is retrieve from table scan(data page),so performance is de-greated.
--Key lookup ,it should be removed from the execution plan to improve performance
--To remove that key lookup-->2 methods:one is cover index(ie;select columns +where columns(Non clutered index),included columns(create non-clustered index)
--Best method is included columns(Creating an included column non-clustered index)
-- I have mentioned in my previous article that I prefer the Method 2. The reason is that method 2 has many advantages over method 1.
--1) Index can exceed the 900-byte limitation of the index key.
--2) Index can include datatypes that are not allowed as key columns – varchar(max), nvarchar(max) or XML.
--3) Size of the key index can be reduced, which improves the overall performance of the index operation.
CREATE NONCLUSTERED INDEX [IX_OneIndex_Include] ON [dbo].[OneIndex]
(
City
) INCLUDE (FirstName,ID) ON [PRIMARY]
GO
Here ID is clustered index and where clause City is also non-clustered index
In earlier article I have suggested to include all columns but in reality in any non clustered index there is no need to include columns included in
clustered index.
--All non clusteredindex automatically contains pointers to clustered index any way.
IE;CREATE NONCLUSTERED INDEX [IX_OneIndex_Include3] ON [dbo].[OneIndex]
(
City
) INCLUDE (FirstName) ON [PRIMARY]
GO
===================================
SELECT ID, FirstName
FROM OneIndex WITH (INDEX(IX_OneIndex_Include3))
WHERE City = 'Las Vegas'
GO
--Let us examine the execution plan and compare the query costs and also verify that if both the index usages are forcing index seek instead of index scan.
=============================================================================================================================
What is covering index?
It is an index that can satisfy a query just by its index keys without having needed to touch the data pages.
It means that when a query is fired, SQL Server doesn’t need to go to the table to retrieve the rows, but can produce the results directly from the index as
the index covers all the columns used in query.
Well, summary is very simple that because of cover index, the performance of the query improves and IO decreases. Included index is another good concept, we
will talk about it in later post.
Before Cover Index: IO Cost 0.006088
logical reads 6, physical reads 3, read-ahead reads 16,
After Cover Index: IO Cost 0.0038657
logical reads 3, physical reads 1, read-ahead reads 0
=====================================================================
--Table spool:stores data from i/p to temporary table in order to optimize rewinds
--Index spool:re-format from i/p to temporary index,which is then used for seeking with the supplied seek predicate
--clustered index seek:scannin particular ranges of rows from clustered index
--Assert:used to verify that specificied condition exists
==========================================================
T-SQL: Simplified CASE expression
1.ISNULL
2.COALESCE
3.IIF
4.CHOOSE
============================================
-- Customers with orders handled by all employees from the USA
-- using literals
SELECT custid
FROM Sales.Orders
WHERE empid IN(1, 2, 3, 4, 8)
GROUP BY custid
HAVING COUNT(DISTINCT empid) = 5;
============
Orders with maximum orderdate for each employee
ans:correlated subquery(max(date) and empid join condition)
-- Comparing Current to Previous Year’s Number of Customers
use multi reference(left join)
===================================
The fact of the matter is that computer systems have always had the GIGO (garbage in garbage out) problem.
the SQL server needed to be restarted. The tables are initially created in the model database and then copied to tempdb after the first restart of SQL
Server. This is one-off process as we built a new server.
----------------------------------------------------------------------------------------------------------------
Change data capture in sql server
exec sys.sp_cdc_enable_db--for enabling CDC on dbase
--For CDC enabled on table
EXECUTE sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'MyOrders',
@capture_instance = N'InsideTSQL2008';
-- =====================================================
-- Determine Whether a Table is Enabled for CDC Template
-- =====================================================
SELECT is_tracked_by_cdc ,*FROM sys.tables
--if u do any operations like Delete,insert,update
--once enabled cdc table will track all the data(by creating one table ie;schemanametablename_CT),it geather last value and updated value if u do update
operation
-----------------------------------------------------------------------------------
there one primaryu key column in a table 6:13 PM
i wnat to create clustered index on that 6:14 PM
*i wnat to create non clustered index on that 6:14 PM
instead of clustered index 6:14 PM
ans: drop the primary key and then only clustered index can be dropped 6:14 PM
then create primary along with non clustered index
Ans:
create table tpk(id int primary key,name varchar(40))
ALTER TABLE [dbo].[tpk] DROP CONSTRAINT PK__tpk__3213E83F3864608B
ALTER TABLE [dbo].[tpk] ADD PRIMARY KEY nonCLUSTERED
(
[id] ASC
)
----------
-- Row Number with Arbitrary Order
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS orderid,
orderdate, custid, empid
FROM sales.Orders;
----------------------------------
--CHECK Constraint
---------------------------------------------------------------------
USE AdventureWorks2008;
GO
SELECT DISTINCT Status FROM Sales.SalesOrderHeader;
--Result: 5 (the status 5 is set for all orders)
--Let's create a CHECK constraint, which disallows values greather than 5 for the status column
ALTER TABLE Sales.SalesOrderHeader WITH CHECK ADD CONSTRAINT chkStatus CHECK (Status < 6);
--Check for rows with the status 4 or 6
SELECT * FROM Sales.SalesOrderHeader WHERE Status = 4;
SELECT * FROM Sales.SalesOrderHeader WHERE Status = 6;
--Result: 100% : 0% (in the first case Clustered Index Scan has been performed, for the second query
-- the table was not touched at all!)
--Clean-Up
ALTER TABLE Sales.SalesOrderHeader DROP CONSTRAINT chkStatus;
-------------------------------------
---------------------------------------------------------------------
--Data Type Conversions
---------------------------------------------------------------------
-- 3.1 nvarchar column and varchar argument - NOTHING happens
--VARCHAR column and NVARCHAR argument - CONVERSION needed
--Result: 5% : 95% (Conversion overhead is significant; non-unicode will be converted to Unicode)
--Logical Reads: 5 vs. 54
--Solution: Use the column data type for argument too, or explicitely convert the argument data type to the column data type
SELECT FirstName, LastName_Varchar FROM dbo.Contacts WHERE LastName_Varchar ='Atkinson';
SELECT FirstName, LastName_Varchar FROM dbo.Contacts WHERE LastName_Varchar = CONVERT(VARCHAR(50),N'Atkinson')
--Constraints and Performance
--Let's create a CHECK constraint, which disallows values greather than 5 for the status column
ALTER TABLE Sales.SalesOrderHeader WITH CHECK ADD CONSTRAINT chkStatus CHECK (Status < 6);
WHERE Status = 4;
WHERE Status = 6;
--Result: 100% : 0% (in the first case Clustered Index Scan has been performed, for the second query
-- the table was not touched at all!)
---------------------------------------------------------------------
--NON-SARG in the WHERE clause
--IN vs. BETWEEN
--Result: Query 1: 2 rows, 6 logical reads
-- Query 2: 2 rows, 3 logical reads (the same plan, but 2 scans for the query 1)
--Use Index for Aggregate Functions
SELECT MAX(TotalDue) FROM Sales.SalesOrderHeader;
--Result: Clustered Index Scan (1406 log. reads)
--Create an index on the TotalDue and try again
IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE name='IX901')
CREATE NONCLUSTERED INDEX IX901 ON Sales.SalesOrderHeader (TotalDue);
GO
SELECT MAX(TotalDue) FROM Sales.SalesOrderHeader;
--Result: Index IX_SalesOrderHeader_TotalDue Scan (2 log. reads)
--Conculsion: Select columns can be also index candidates if they are part of aggregate functions
--verify existance
--Result: 50% : 50% (SQL Server does not count the rows - it stops when the first has been found)
--But if you use local variable to hold COUNT(*), of course it will count the rows
IF ((SELECT COUNT(*)
-- =============================================
-- Add another Sql Server as linked server
-- =============================================
sp_addlinkedserver N'<server_name, sysname, server1>', N'SQL Server'
GO
-- Drop linked server
-- =============================================
sp_dropserver @server = N'<server_name, sysname, server1>',
@droplogins = <option, char(10), NULL>
-- Configure linked server
-- =============================================
sp_serveroption @server = N'<server_name, sysname, server1>',
@optname = '<option_name, varchar(35), dist>',
@optvalue = N'<option_value, nvarchar(128), OFF>'
-- =============================================
-- List linked server
-- =============================================
sp_linkedservers
GO
-- Declare and using a KEYSET cursor
-- Declare and using a READ_ONLY cursor
--Declare and using an UPDATE cursor
-- Declare a SCROLL cursor and use various FETCH options
DECLARE <cursor_name, sysname, test_cursor> SCROLL CURSOR FOR
<select_statement, , SELECT * FROM pubs.dbo.authors>
OPEN <cursor_name, sysname, test_cursor>
-- Fetch " " in the cursor.
FETCH FIRST FROM <cursor_name, sysname, test_cursor>
--FETCH First,last,prior,next,absoute(rownum),relative(rownum) from cursorname
CLOSE <cursor_name, sysname, test_cursor>
DEALLOCATE <cursor_name, sysname, test_cursor>
---dbase
--==================================
-- Take database offline template
--==================================
ALTER DATABASE <Database_Name, sysname, Database_Name>
SET OFFLINE
--Drop dbase
DROP DATABASE <Database_Name, sysname, Database_Name>
--=====================================
-- Detach database template
--=====================================
EXEC sp_detach_db @dbname = '<database_name, sysname, your_database_name>'
,@skipchecks = 'true'
,@KeepFulltextIndexFile = 'true'
--==================================
-- Bring database online template
--==================================
ALTER DATABASE <Database_Name, sysname, Database_Name>
SET ONLINE
GO
--=====================================
-- Attach database template
--=====================================
IF NOT EXISTS(
SELECT *
FROM sys.databases
WHERE name = N'<database_name, sysname, your_database_name>'
)
CREATE DATABASE <database_name, sysname, your_database_name>
ON PRIMARY (FILENAME = '<database_primary_file_path,,C:\Program files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data
\your_database_name.MDF>')
FOR ATTACH
GO
No comments:
Post a Comment