Transact SQL :: How To Find If A Column Is Used Anywhere In Database

Oct 20, 2015

I have looked around quite a bit, but mostly what I have found is looking to see if a table is used or if a column is in a stored procedure and honestly most of what I have seen does not work.

I want to reduce our nightly import by removing any columns that are not being used. We insert into our staging tables, Stage1 for example. And say Stage1 has column1 and column2. If those columns are not being used, then I want to remove them from Stage1. The only catch is that every Stage1 table has a v_Stage1. v_Stage1 should have all the columns from Stage1, but doesn't always. So I need to know what columns from Stage1 are used somewhere other than v_Stage1 and what columns from v_Stage1 are not used. 

View 14 Replies


ADVERTISEMENT

Transact SQL :: Find Unique Rows In Column That Are Associated With Another Field In Another Column?

May 1, 2015

I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1.  To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique. 

Table1

GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2

The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.

Result:

PID1 

View 2 Replies View Related

Transact SQL :: How To Find Combination Of Column - Row Unique

Mar 8, 2012

I have table with some columns with no primary key..

I have to find the combination of columns which may the row unique...

How to do that like microsoft...

View 14 Replies View Related

Transact SQL :: Find Table And Column Names From String Data

May 29, 2015

I have a SQL text column from SP_who2 in table #SqlStatement:

like 1row shown  below :

 "update Panel  set PanelValue=7286 where PanelFirmwareID=4 and PanelSettingID=9004000"

I want to find what table and column  names are in the text ..

I tried like below ..  

Select B.Statement from #sp_who2 A  
LEFT JOIN #SqlStatement B ON A.spid = B.spid 
 where B.Statement IN (
SELECT T.name, C.name FROM sys.tables T
JOIN sys.columns C 
ON T.object_id=C.object_id
WHERE T.type='U'


Something like this : find the column names and tables name

View 18 Replies View Related

Transact SQL :: How To Find Whether A Column Lies In Range Of Smallint / Int / Bigint

May 12, 2015

UPDATE P   
SET  
P.IsError=1
,P.IsDrawingRevNo=1 
,ErrorMessage=ISNULL(ErrorMessage,'')+'| DrawingRevisionNumber DataType Is Not Valid, smallint expected(-32768 AND 32767)'
FROM ZPTSMGR.ProjectDrawingRaw P
WHERE  P.LogId=@LogId AND   P.ProjectId=@ProjectId AND  P.Revision > 32767   (P.Revision  NOT BETWEEN  -32768 AND 32767)  --SMALLINT RANGE  -32768 to 32767.

--DataType Range
--tinyint DataType  (MinVal: 0, MaxVal: 255). Its storage size is 1 byte.
--smallint DataType from -2^15 (-32,768) through 2^15 - 1 (32,767) and its storage size is 2 bytes.
--int DataType   -2^31(-2,147,483,648) to 2 ^31-1(2,147,483,647). Its storage size is 4 bytes.
--Bigint DataType -- from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Its storage size is 8 bytes.

The SQl statement fails, and not able to update it. The IsError flag need to set since the value does not lies in given range of smallint.--------say 457896523 which is not a small int value

View 7 Replies View Related

Transact SQL :: Find Column ADRSCODE Used In Indexes Or Primary Keys?

Oct 12, 2015

I wanted to find all occurrences of ADRSCODE in a Database where ADRSCODE is in either an Index or a Primary Key.

I know how to get all of the occurences of ADRSCODE in a database and the table associated with it, I just want to tack on the Index and/or primary key.

SELECTOBJECT_NAME(object_id)FROMsys.columns
WHEREname
='foo'

How can I get the other bit of information ?

View 2 Replies View Related

Transact SQL :: Find Unmatching Columns Between Two Tables While Applying A Change Column Prefix Table

Nov 16, 2015

For our ETL process, we maintain a TransformationList table that has the source view and the destination table. Data is copied from the view into the table (INSERT INTO). I am trying to find column names in the Views that are not column names in the associated Table.

In the below example, want to end up with three records:

 1, View1, Column4
 2, View2, Column4
 2, View2, Column5

I have it almost working, except that there is a table, ChangeColPrefix table, that is used by the ETL process to change some of the view's column name prefixes. Some of the source views have column names with prefixes that do not match the destination table column names. Say view SouthBase has all the column names prefixed with SB - like SBAcct, SBName. And the Destination table of Area District has ADAcct, ADName. There would be a row in the ChangeColPrefix for SouthBase, SB, AD, 1, 2 that would be used by the ETL process to create the INSERT INTO Area District From SouthBase.

I need to use this ChangeColPreifx to find my unmatching columns between my source views and destination tables. With out that table SBAcct and SBName from SouthBase will not appear to match the columns of ADAcct and ADName, but they do match.

I want to end up with these three records as non-matching:

View1, Column4
View2, Column4
View2, Column5

View1 has Salumn2 and View2 has Salumn5, and they must be changed to Column2 and Column5 as per the ChangeColPrefix table before running the Select from INFORMATION_SCHEMA.COLUMNS EXCEPT Select from INFORMATION_SCHEMA.COLUMNS looking for unmatched columns.

/***** Set Up Test Data *****/
-- Create 2 test views
IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View1]'))
DROP VIEW dbo.[View1]
GO
CREATE VIEW View1
AS SELECT '1' AS Column1 , '2' AS Salumn2 , '4' AS Column4;

[Code] ....

View 3 Replies View Related

Transact SQL :: Query To Find Out Database Usage

Sep 15, 2015

I can use Profiler to see database usage activity. However, in addition to it, is there a good query I can use to see whether user databases are being used (last select, last update, last alter or last delete etc., with date/time stamp)?I am looking for both SQL2000 and SQL2005 as we need to decommission some of the older servers.

View 6 Replies View Related

Transact SQL :: How To Find All Dynamic Scripted Stored Procedure In Database

Jul 2, 2015

Is there any way/Process that we can find out all stored procedures from database where dynamic script has used in script.

View 6 Replies View Related

Transact SQL :: How To Find Stored Procedures Without NOLOCK Statements In Database

Jun 15, 2015

I need to list out all the procedures which have select queries and does not use NOLOCK hint. Please note that there are more than 2000 sps and some sps have single select some have multiple, some does not have select queries at all. I need to find out only the ones which does not use NOLOCK hint in a select query.

View 8 Replies View Related

How To Find A Column In Database

May 29, 2008

Hi all,
Is there a way to see how many tables have a column like "name" in database?
Thanks

View 5 Replies View Related

Transact SQL :: Script To Find List Of Queries Currently Running In Database With User Login And Host Name?

Dec 30, 2014

How to find the list of queries currently running in the Database with User Login Information.

Since my database application is running slow, to find the slow queries.

View 8 Replies View Related

Transact SQL :: Find Latest Start Date After A Gap In Date Column

Apr 19, 2015

My requirement is to get the earliest start date after a gap in a date column.My date field will be like this.

Table Name-XXX
StartDate(Column Name)
2014/10/01
2014/11/01
2014/12/01

[code]...

 In this scenario i need the latest start date after the gap ie. 2015/09/01 .If there is no gap in the date column i need 2014/10/01

View 10 Replies View Related

Transact SQL :: Fragmentation Levels Of All The Clustered Column-store Indexes In A Database?

Sep 18, 2015

I have a  database in which I have  some tables in which I have implemented Clustered columnstore Index. How to find the fragmentation levels of all these indexes via  a single T-SQl script

View 3 Replies View Related

Transact SQL :: Getting List Of Clustered Column-store Index In A Database In 2014?

Sep 17, 2015

How can we get the list of clustered columnstore index in a database in sql server 2014

View 3 Replies View Related

Query To Find A Value In Column B Based On An Aggregate Function On Column A?

Jul 20, 2005

Hi,Suppose I have a table containing monthly sales figures from my shopbranches:Branch Month Sales-----------------------London Jan 5000London Feb 4500London Mar 5200Cardiff Jan 2900Cardiff Feb 4100Cardiff Mar 3500The question I am trying to ask is this: in which month did each branchachieve its highest sales? So I want a result set something like this:Branch Month----------------London MarCardiff FebI can do a "SELECT Branch, MAX(Sales) FROM MonthlySales GROUP BY Branch" totell me what the highest monthly sales figure was, but I just can't figureout how to write a query to tell me which month corresponded to MAX(Sales).Ideas anyone?Cheers,....Andy

View 5 Replies View Related

Transact SQL :: Calculate DateTime Column By Merging Values From Date Column And Only Time Part Of DateTime Column?

Aug 3, 2015

How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?

View 5 Replies View Related

Transact SQL :: How To Find A Node

Nov 8, 2015

i have a table below like :
Id    ,    Request   

int     nvarchar(100)   
and in Request field i put below data :
1 <request><F3>353535</F3><F6></F6></request> 2 <request><F5>353535</F5><F6></F6></request>3 <request><F3>353535</F3><F6></F6></request>

now i need to a query that i can find records that exists <F3> and if exists , remove just the <F3> tag

 below like :
1 <request><F6></F6></request> 2 <request><F5>353535</F5><F6></F6></request>3 <request><F6></F6></request>

View 18 Replies View Related

Find Duplicate In Column Tied To Another Column

Apr 29, 2015

I have one table with two columns, GUID and PID and another table with three columns, GUID, LastName, and FirstName. For each unique PID, I need to find the PID's that have more than one GUID and then match that with their respective FirstName and LastName from the other table.

Table1

GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2

Table2

GUID LastName FirstName
GUID1 Mulder Fox
GUID2 Scully Dana
GUID3 Skinner Walter

So I'm looking for a result like:

PID1 GUID 1 Mulder Fox
----- GUID 2 Scully Dana

View 5 Replies View Related

Transact SQL :: How To Find Duplicates For Different Fields

Oct 27, 2015

I have a table that has Serial numbers and MSDSID numbers and many other fields in the table.

What I need to figure out is if the table contains a distinct Serial number with different MSDSIDs.

So If I have in the table

Serial          MSDSID          Date          Size...
001                 20            1/1/2015        5
002                 21            1/1/2015        3
001                 22            2/1/2015        1
003                 21            3/1/2015        1
004                 23            1/15/2015      5
003                 22            1/20/2015      6
004                 23            2/21/2015      5
002                 21            4/25/2015      4

I would like the results to show:

Serial            Count
001                  2           the count is 2 because Serial 001 has an MSDSID of 20 and 22
002                  1           the count is 1 because Serial 002 only has MSDSID 21
003                  2           the count is 2 because Serial 003 has an MSDSID of 21 and 22
004                  1           the count is 1 because Serial 002 only has MSDSID 23

It would be even better if the results just showed where the count is greater than 1.

View 5 Replies View Related

Transact SQL :: How To Find Mail Is Queued

Jul 19, 2015

I want to send an email twice a day, from database. So I have planned to make a storedproce which will be called by a job (which will select some record from one table and put it in other table based on a flag) but I want to run it in a transaction so that if email is send successfully then only it should commit else it should rollback.

How  can i find that "Mail queued" now i should commit.

View 4 Replies View Related

Transact SQL :: How To Find Alphanumeric Formats

Jun 12, 2015

create table example (f varchar(10))

insert into example values('fd')
insert into example values('fd')
insert into example values('fd1')
insert into example values('fd23')
insert into example values('fda23')
insert into example values('fd23g')

output:-

[A-Z][A-Z]
[A-Z][A-Z]
[A-Z][A-Z][0-9]
[A-Z][A-Z][0-9][0-9]
[A-Z][A-Z][A-Z][0-9][0-9]
[A-Z][A-Z][0-9][0-9][A-Z]

How to write SQl query to fetch this type of output.

View 6 Replies View Related

Transact SQL :: Find Max Of 2 Columns In One Table?

Jul 23, 2015

I am having trouble trying to find the max of 2 columns in one table. I've tried using a common table expression and a subquery, but can't seem to get the correct results. I want to get the max from refnum, then the max "number" associated with that max refnum along with the date and decision

Table
IDCustomerRefnumnumberdate decision
16511114/17/2015Approved
16521125/1/2015Declined
16531216/10/2015Approved
16542116/15/2015Tentative

Expected

Customer 1 had a max of refnum of 2 and 1st one on number

IDCustomerRefnumnumberdate decision
16531216/10/2015Approved
16542116/15/2015Tentative

View 21 Replies View Related

Transact SQL :: Find Duration From Two Records

Oct 26, 2015

I am working on one logic. I have a table as below.

Declare @Table table (ID int, StartDate datetime, EndDate datetime)
Insert Into @Table (ID, StartDate, EndDate)
Values (1, '2013-01-01', '2013-12-31') 
, (1, '2014-01-01', '2014-06-30') 
, (2, '2014-01-01', '2014-07-31') 
, (2, '2014-08-02', '2014-08-30') 
select * from @Table 

I need as below.

ID, StartDate, EndDate
1, '2013-01-01', '2014-06-30'
2, '2014-01-01', '2014-07-31'
2, '2014-08-02', '2014-08-30'

Means If ID is same for two or more than two records then difference between first row's EndDate and second row's StartDate is 1 day then we should get one record as output. How can we built this logic in T-SQL ?

View 12 Replies View Related

Transact SQL :: Find Combination Of Rows?

May 10, 2015

I have this data as below. I need to find out the combination from the data and take a count of them

CREATE TABLE A
(    nRef INT,
     nOrd     INT,
     Token     INT,
     nML              INT,
     nNode            INT,
 sSymbol          VARCHAR(50),
 nMessageCode     INT
)
INSERT INTO A
(   nReferenceNumber,nOrderNumber,nTokenNumber,nML,nNode,sSymbol,nMessageCode )
VALUES
(1, 101, 1001,0,2,'SILVER',13073),

[code]....

if you can see, the rows with column nRefNo 1 and 3 are same i.e. with same combination of Symbol viz. Silver and Castorseed. How to get this combination together and then take count of them. Please note i will be dealing with more than 5 million rows.

View 6 Replies View Related

Transact SQL :: Find How Many Rows Replicated

Aug 20, 2015

How Do we find how many ROWS REPLICATED to subscriber at specific interval ?

View 3 Replies View Related

Transact SQL :: How To Find Transaction In A Backup

Oct 22, 2015

I have heard about fn_dblog() function, Is it possible to find a transaction in a backup using fn_dblog() function?

View 3 Replies View Related

Transact SQL :: SUM Of Two Table Column Base On Another Column Value And SUBTRACT And Join Tables

Oct 14, 2015

I have the following table

Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
    1             |John                       |Baker
    2             |Carl                        |Lennon
    3             |Marion                    |Herbert

Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
    1        |1/1/14      |12/30/14
    2        |1/1/15      |12/30/15

[code]...

I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID  AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID  AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID  AS SickBalance

and the table should be shown as below after executing the query

EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance

View 4 Replies View Related

Transact SQL :: How To Find A Non Matched Record In 2 Tables

Jul 15, 2015

I have 2 tables .Lets Say tableA and tableB.Both Have Columns ClaimNumber,Amount. Now, to get the matched records for these 2 tables, i wrote the following query Select * from tableA A Inner Join tableB B on A.ClaimNumber = B.ClaimNumber and A.Amount = B.Amount This query works perfectly fine and gives me only matching records, however if i want to have records which match with ClaimNumber and not with Amount i wrote something like this

Select * from tableA A Inner Join tableB B on A.ClaimNumber = B.ClaimNumber and A.Amount <> B.Amount.

And this query produces wrong results, its giving me match and also non match records.

how to write a query for my non match condition?

View 5 Replies View Related

Transact SQL :: How To Find If User Not Logged In For 45 Days

Nov 20, 2015

I have created table called Login in sql server where i have column usercode, email and login_date (login_date is datetime type)So, i created web application using .net. whenever user logged in, i am allowing based userLoged table and  i am inserting into login table.

login table usercode  email       login_date
001          a@gmail.com    2015-11-18 22:02:41.153
001          a@gmail.com    xxx
.
.
.

I have another table called userLoged where i have column usercode,email and web_access 

UserLoged table usercode email         web_access
001         a@gmail.com   Y

Now, if a@gmail.com (001) is not logged in for 45 days, i need to update web_access to be 'N' how to know if he /she not logged for 45 days.

View 4 Replies View Related

Transact SQL :: Find A Count On Group Of Member IDs

Aug 20, 2015

I am trying to find a count on group of our memberid`s who were active within a year since 2010 till today for particular memberships in my table I have memberid int effectivedate datetime termdate datetime Membershiptype varchar(10) ='GOLD','Silver' and 'Platinum'. I haven't used sql in a long time..

View 6 Replies View Related

Transact SQL :: Find All Duplicate Entries In Table

Jun 11, 2015

Someone ran an update statement multiple times so their are multiple entries in the table.  What is the quickest way to track down the multiple entries?  I would only want to see where timein and timeoff exist in the table multiple times for the same id.  So this would be a duplicate

EntryID -- ID  -- timein -- timeoff
1487   11     2015-05-05 16:33:23   2015-05-05 18:45:26
1623   11     2015-05-05 16:33:23   2015-05-05 18:45:26

View 7 Replies View Related

Transact SQL :: How To Find Duplicates For Multiple Fields

Oct 26, 2015

I have a table that exists of Serial, MSDSID and other fields.

What I need to check is to see if there are Serial numbers that multiple MSDSID numbers.

So if I have

Serial          MSDSID
001                23
002                24
003                25
004                23
005                26
006                24

The results would be

Serial             MSDSID           Count
001                23                   2
004                23                   2
002                24                   2
006                24                   2

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved