When I open a table and need to make a change within a particular record...
How do I find the particular row that I am looking for? I seem to have to scroll through 600 records to find the one I need. I've found a search and replace function but it doesn't seem to work.
Thanks I appreciate any advice you can give me.
Can this be done easilly through a stored proc?
View 1 Replies View Related
I have 2 tables with 2 fields in common between them. How do I get a count of rows in table 1 but not in table 2 matching those 2 fields? Thanks.
Hi, I need to write a query which I have never attempted before and could do with some help.... I have a Groups table and a Users_Groups look up table. In this model, users can only be assigned to 1 group. If a group is deleted, a trigger should fire and delete any rows in User_Groups having a matching Groups.Ref. Unfortunately, the trigger hasn't been firing and I now have a load of defunct rows in Users_Groups relating users to groups which do not exist.I now need to find all of these defunct rows in Users_Groups so that I can delete them. How can I find rows in Users_Groups where the parent rows and refs in Groups are null? I've tried searching the net for something similar but don't even know how to word the search properly to get any half relevant results. Cheers PS, I do realise I need to tighten the constraints on my database
View 5 Replies View RelatedI have a split string function that will take a comma delimited string and give back a table with all the values.I have a table that has a column with a comma delimited comma delimited list of states.
I can use a where clause to find one state in the table (such as all records that have CA in the states string).But need to find out how to find all the rows that have all or any of the states from a comma delimited parameter.Here is the schema
CREATE FUNCTION [dbo].[split] (@list nvarchar(MAX))
RETURNS @tbl TABLE (svar nvarchar(10) NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int
[code]....
Hi guys,This might be simplest thing, but I am newbie to databases.I need to find out only rows modified within certain time period from adatabase. As I undertand a way out could be adding an where clause forthe time period might be an option, I might be wrong here again.But, wanted to know is there any other option. Can triggers or anyother things help me in this matter.Regards,Abhijeet
View 1 Replies View RelatedI'm got a "folder" structure application which we'll be using as an in-house directory viewer. (In case you're wondering, it doesn't relate to any "real" folders, so using xp_cmdshell is out! )
Each folder and file record can have its own permissions, however these are assumed to inherit from the parent folder if no specific access rules have been set, basically in the same way file systems work. Each file record can only have one parent, and a folder can either have a parent or be at the root level.
Right now I'm having an issue with the inheritance of permissions. Say if I want to grant access to "Folder 1" to "Group A", then "Group B" shouldn't be able to see it. However, if I grant access to "File 1" in "Folder 1" to "Group B", then "Group B" should be able to see "Folder 1", but only see "File 1" and not the rest of the contents.
I thought I could do this with a CTE, but I'm having a bit of difficulty..
Here's the code:
CREATE TABLE #FileSystem (
FSIDINTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY
,ParentFSIDINTEGER NULL
,NameVARCHAR(100)
,RecordTypeVARCHAR(1)-- (F)older, or Fi(L)e
[Code] ....
CREATE TABLE [RS_A] ([ColA] [varchar] (10)[ColB] [int] NULL)CREATE TABLE [RS_B] ([ColA] [varchar] (10)[ColB] [int] NULL)INSERT INTO RS_AVALUES ('hemingway' , 1)INSERT INTO RS_AVALUES ('vidal' , 2)INSERT INTO RS_AVALUES ('dickens' , 3)INSERT INTO RS_AVALUES ('rushdie' , 4)INSERT INTO RS_BVALUES ('hemingway' , 1)INSERT INTO RS_BVALUES ('vidal' , 2)I need to find all the rows in A which do not exist in Bby matching on both ColA and ColBso the output should bedickens 3rushdie 4So if i write a query like this , I dont get the right result setSELECT A.ColA, A.ColBFROMRS_A AINNERJOIN RS_B BONA.ColA <B.ColAORB.ColB <B.ColBBut if i do the following, i do get the right result, but followingseems convoluted.SELECT A.ColA, A.ColBFROMRS_A AWHERE ColA + CAST(ColB AS VARCHAR)NOT IN (SELECT ColA+CAST(ColB AS VARCHAR) FROMRS_B B)
View 6 Replies View RelatedI have a script that creates these spin off tables (used for lightning fast access) on a near daily basis. But it is possible the table does not exist for that day - in which the script need to check one day back - so on and so forth until finding the most recently created table.
The script I have written fails with overflow error.
I was hoping someone could tell me the correct syntax to accomplish this and also why i recieve this overflow error...
Code:
Dim str_SQL_Asset_1
daysBack = 1
tblFound = FALSE
Dim xbl_Holdings_date
Dim xMonth
Dim xDay
xbl_Holdings_date = date()
If Len(Month(xbl_Holdings_date))=1 Then
xMonth = "0" & Month(xbl_Holdings_date)
Else
xMonth = Month(xbl_Holdings_date)
End If
If Len(Day(xbl_Holdings_date))=1 Then
xDay = "0" & Day(xbl_Holdings_date)
Else
xDay = Day(xbl_Holdings_date)
End If
DO WHILE tblFound = FALSE
xbl_Holdings_Name = "xbl_Holdings_" & Year(xbl_Holdings_date) & "_" & xMonth & "_" & xDay
SQL_tblCheck_STR = "SELECT * FROM INFORMATION_SCHEMA.TABLES " & _
"WHERE table_name = '" & xbl_Holdings_Name & "' "
Set MM_checkCmd = Server.CreateObject("ADODB.Recordset")
MM_checkCmd.ActiveConnection = conn_str_ClientDB
MM_checkCmd.Source = SQL_tblCheck_STR
MM_checkCmd.CursorType = 0
MM_checkCmd.CursorLocation = 2
MM_checkCmd.LockType = 3
MM_checkCmd.Open()
IF MM_checkCmd.EOF Then
xbl_Holdings_date = (xbl_Holdings_date - daysBack)
daysBack = daysBack + 1
tblFound = FALSE
ELSE
tblFound = TRUE
MM_checkCmd.CLOSE()
SET MM_checkCmd = Nothing
END IF
loop
The goal of this snippet is simply to identify and set the value for the existing table name.
An example table name would be "xbl_Holdings_2006_12_10".
I could have posted this question in the VB forum but I would like to push as much of the work onto SQL as possible and also - this sort of operation is more likely to be required of a dba imo.
Is there a way i can create a query that gives me a list of all the table names in the database?
View 2 Replies View RelatedI'm trying to find the primary key on a given table in SQL Server 2000 using SQL. I'm querying the sysobjects table to find a given table, and then querying the sysindexes table. I've ALMOST found what I'm looking for. I see the indexes and columns etc. on the tables in the database, I just don't see the field that indicates that the index is the primary key.
Can anyone help?
Thanks, Alex
Hi All,
Do any of you know how I can find the size of a table in SQL server?
Cheers
Dave
I'm fairly new to sql.I've got a database that all I want to do is view some records fromsome tables. But the thing is, I do not know the table names ortheir relationships.I've got access to the database thru query analyzer. Can someoneassist me in gettign the correct statements that will allow me to findout what tables exist in the database.
View 4 Replies View RelatedI'm new here so bare with me for alittle bit.
When I go to a website how to I find out the table names in the data?
I mean how can I do "select * from ___" if I don't know the table name.
Thank you for any help,
S_R_S
Hi,
When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.
How can we handle transactions in SSIS
1. when some error/something happens during export and the # of rows are not exported fully to destination, how to rollback the transaction in SSIS.
Any sort of help would be highly appreciated.
Thanks,
Hi,
When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.
Any sort of help would be highly appreciated.
Thanks,
Need some suggestions on what to check or look for.My stored proc is not finding the table in the sql db. This is the error...Invalid object name 'tblServRec'I use the same function to pass in an arraylist and the sp name for another sp and that one works ok.The sp is the same as another one except for the table name and that one works ok.The sp works fine on my local machine. It finds the table ok. But when I try it on the server, it doesn't work.I have checked permissions, and they are the same for all tables, even the one that the other sp works ok on the server.Here is the sp if that will help.....CREATE PROCEDURE dbo.deleteServRec
@fldNum INT ASBEGIN DECLARE @errCode INT
DELETE FROM tblServRec WHERE fldNum = @fldNum SET @errCode = 0 RETURN @errCode
HANDLE_APPERR:
SET @errCode = 1 RETURN @errCodeENDGOThanks all,Zath
Hi Friends,
Is there any way to find the time when the last DDL was happened in a table?
For example: The time when the new column(s) were added into a table or changed the datatype.
Thanks for the help in advance.
--Raj
Is there a way to find out when the last modification or change to data in a table occured ? How about the last change of a view ?
We'd like to be able to extract data to another table based on a view. Then, at a later date, check to see if the first table or view has changed since the last extract. If not, then we don't need to re-extract the data, we know it hasn't changed.
Hii all,
I want to find the database , table size in MS SQL Server.
Is there is any query /procedures to find out the database and table size.
Thanks in advance .,
Regards.,
Sri
finding the first value to cross a specific threshold in SQL Server
LoadTemp1 LoadTemp2 LoadTemp3 TimeStamp
300 350 200 12-10-2013 13:30
177 238 333 12-10-2013 13:05
240 190 170 12-10-2013 12:45
How do I design query to find the first LoadTemp* that cross a threshold of 340 degrees per TimeStamp.
Hi i've to table where i have a product code and a area code.
I want to insert the values from table 1 into table 2 if the product code and area code do not already exists in the table in table 2.
Am using this code below but it does not seem to be working.
select distinct p_code1, area_code
from table1
where not exists
(select p_code, area_code
from table 2 )
A client has a site, that requires users to log in, and each login is logged with IP in this table:
CREATE TABLE [dbo].[Event](
[EventId] [int] IDENTITY(1,1) NOT NULL,
[EventTypeId] [int] NOT NULL,
[IP] [varchar](50) NULL,
[UserId] [int] NULL
[DateCreated] [datetime] NOT NULL CONSTRAINT [DF_Event_DateCreated] DEFAULT (getdate()),
CONSTRAINT [PK_Event] PRIMARY KEY CLUSTERED
(
[EventId] ASC
)
How the client wants me to write a query, that will find "suspected abuse of accounts" - defined by being successful logins made within one hour from at least 3 different IPs.
So I need to check for events, that are within one hour from each other (datecreated field) and have the same userID - and EventTypeId - but at least 3 different IPs.
Maybe it's just late in the day or I am overworked, but I cant seem to find an effective way to approach this.
Ideas?
Hi,I want to find out in my SP the filegroup associated with a particulartable? I was not able to establish the relationship between sysobjectsand sysfilegroups table. Is there any other way to find filegroup of atable in T_SQL?Also lets say there are 3 dtatfiles in a filegroup 'FG1' and table'xyz' is created on filegroup 'FG1' - Is there any way to find out thedatafile's names on which table xyz's data is scattered?Thanks in Advance,Subodh
View 1 Replies View RelatedWe have an application that accesses a particular table, but it isnever updated. Without stepping through the complex application code,is there a method of logging when a particular table is accessed?
View 3 Replies View RelatedI have a SQL script to insert data into a table as below:
INSERT into [SRV1INS2].BB.dbo.Agents2
select * from [SRV2INS14].DD.dbo.Agents
I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.
USE [BB]
GO
/****** Object: Trigger Script Date: 24/07/2015 3:41:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
Hi,
My database is case insensitive. However, the application is case sensitive for the data in the table.
I need to find out the data from the table where the data is stored in lower case.
Here is the sample data:
Table: inv_manager
column: sku varchar(5)
Data: 1134X
1135x
1123a
b145Y
I just need query to return row 2, 3, 4.
Here is my query:
select sku from inv_manager where sku like lower('%[a-z]%')
The problem is it returns all the rows from the table including first one.
How can I return just the data with lower case ?
Thanks
If I have a table of 1,000,000 rows how I do find out what size this table is on disk?
And how do I find out the size of all tables on disc?
I'm trying to find out less than 10% in revenue accounts from a table. Below is a snapshot. Basically, I want to add Revenue mix column in the table using procedure.
ACCOUTSREVENUEREVENUEMIX
ACCOUNT1 100 2%
ACCOUNT2 200 4%
ACCOUNT3 500 9%
ACCOUNT4 1000 19%
ACCOUNT5 1500 28%
ACCOUNT6 2000 38%
TOTAL 5300 100%
I have a table which maps two related IDs. That table has 3 columns: ID, BHID & EPID. I need to find all of the BHIDs where the EPID is unique. It seems easy enough, but I keep going in circles..
USE [CGB]
GO
/****** Object: Table [dbo].[ePID_BHID] Script Date: 04/15/2015 15:48:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
This function will generate all DELETE statements in correct order to perform a CASCADING delete.
For self-joined tables, it will generate the T-SQL code to "unwind" the table, also in correct order!CREATE FUNCTION dbo.fnCascadingDelete
(
@Schema NVARCHAR(128) = NULL,
@Table NVARCHAR(128) = NULL
)
RETURNS@Return TABLE
(
RowID INT PRIMARY KEY CLUSTERED,
IsSelfJoin TINYINT NOT NULL,
HasPk TINYINT NOT NULL,
[SQL] NVARCHAR(4000) NOT NULL
)
AS
BEGIN
DECLARE@Constraints TABLE
(
RowID INT NOT NULL,
Indent SMALLINT NOT NULL,
[Catalog] NVARCHAR(128) NOT NULL,
[Schema] NVARCHAR(128) NOT NULL,
[Table] NVARCHAR(128) NOT NULL,
[Column] NVARCHAR(128),
pkCatalog NVARCHAR(128),
pkSchema NVARCHAR(128),
pkTable NVARCHAR(128),
pkColumn NVARCHAR(128),
pkType NVARCHAR(128),
pkSize INT,
IsSelfJoin TINYINT NOT NULL,
HasPk TINYINT NOT NULL
)
INSERT@Constraints
(
RowID,
Indent,
[Catalog],
[Schema],
[Table],
[Column],
pkCatalog,
pkSchema,
pkTable,
pkColumn,
pkType,
pkSize,
IsSelfJoin,
HasPk
)
SELECTRowID,
Indent,
[Catalog],
[Schema],
[Table],
[Column],
pkCatalog,
pkSchema,
pkTable,
pkColumn,
pkType,
pkSize,
SelfJoin,
CASE
WHEN [Column] IS NULL THEN 0
ELSE 1
END
FROMdbo.fnTableTree(@Schema, @Table)
IF @@ROWCOUNT = 0
RETURN
DECLARE@SQL TABLE
(
ID INT IDENTITY(1, 1),
RowID INT PRIMARY KEY CLUSTERED,
IsSelfJoin TINYINT NOT NULL,
HasPk TINYINT NOT NULL,
[SQL] NVARCHAR(4000) NOT NULL
)
DECLARE@Indent SMALLINT,
@RowID INT,
@ID INT,
@TSQL NVARCHAR(4000),
@RowSQL NVARCHAR(4000),
@EndSQL NVARCHAR(4000),
@pkColumn NVARCHAR(128),
@IsSelfJoin TINYINT,
@HasPk TINYINT
DECLARE@Unwind TABLE
(
RowID INT NOT NULL,
StepID INT IDENTITY(0, 1) PRIMARY KEY NONCLUSTERED,
[SQL] NVARCHAR(4000)
)
WHILE NOT EXISTS (SELECT * FROM @SQL WHERE RowID = 1)
BEGIN
SELECT TOP 1@RowID = c.RowID,
@ID = c.RowID,
@Indent = c.Indent,
@TSQL = N'',
@EndSQL = N'',
@IsSelfJoin = c.IsSelfjoin,
@HasPk = c.HasPk
FROM@Constraints AS c
LEFT JOIN@SQL AS s ON s.RowID = c.RowID
WHEREs.RowID IS NULL
ORDER BYc.Indent DESC,
c.RowID DESC
WHILE @ID > 0
BEGIN
IF @Indent = 0
SELECT@RowSQL = N'DELETE t' + CAST(@RowID AS NVARCHAR(12)),
@RowSQL = @RowSQL + N' FROM ' + QUOTENAME(c.[Catalog]) + N'.' + QUOTENAME(c.[Schema]) + N'.' + QUOTENAME(c.[Table]) + N' AS t' + CAST(@ID AS NVARCHAR(12)),
@EndSQL = N' WHERE t' + CAST(@ID AS NVARCHAR(12)) + '.' + QUOTENAME(COALESCE(c.[Column], '%0')) + N' = ''%1''',
@IsSelfJoin = @IsSelfJoin | c.IsSelfJoin
FROM@Constraints AS c
WHEREc.RowID = @ID
ELSE
SELECT@RowSQL = N' INNER JOIN ' + QUOTENAME(c.[Catalog]) + N'.' + QUOTENAME(c.[Schema]) + N'.' + QUOTENAME(c.[Table]),
@RowSQL = @RowSQL + N' AS t' + CAST(@ID AS NVARCHAR(12)) + N' ON t' + CAST(@ID AS NVARCHAR(12)) + N'.' + QUOTENAME(c.[Column]),
@pkColumn = QUOTENAME(c.pkColumn),
@IsSelfJoin = @IsSelfJoin | c.IsSelfJoin
FROM@Constraints AS c
WHEREc.RowID = @ID
SELECT TOP 1@ID = c.RowID,
@Indent = c.Indent,
@RowSQL = @RowSQL + N' = t' + CAST(c.RowID AS NVARCHAR(12)) + N'.' + @pkColumn,
@IsSelfJoin = @IsSelfJoin | c.IsSelfJoin
FROM@Constraints AS c
WHEREc.RowID < @ID
AND c.Indent < @Indent
ORDER BYc.Indent DESC,
c.RowID DESC
IF @@ROWCOUNT = 0
SET@ID = 0
SET@TSQL = @RowSQL + @TSQL
END
INSERT@SQL
(
RowID,
IsSelfJoin,
HasPk,
[SQL]
)
VALUES(
@RowID,
@IsSelfJoin,
@HasPk,
@TSQL + @EndSQL
)
IF @IsSelfJoin = 1
BEGIN
DECLARE@Yak NVARCHAR(160),
@Catalog NVARCHAR(128),
@Column NVARCHAR(128)
SELECT@Yak = pkType + COALESCE('(' + CAST(pkSize AS NVARCHAR(12)) + ')', ''),
@Catalog = [Catalog],
@Schema = [Schema],
@Table = [Table],
@Column = [Column],
@Catalog = [Catalog],
@Table = [Table],
@pkColumn = pkColumn
FROM@Constraints
WHERERowID = @RowID
SET@RowSQL = 'DECLARE@Lvl INT
SET@Lvl = 0
DECLARE@Stage TABLE (RowID INT IDENTITY(0, 1), Lvl INT, RowKey ' + @Yak + ')
INSERT @Stage (Lvl, RowKey) '
+ REPLACE(@TSQL + @EndSQL, 'DELETE t' + CAST(@RowID AS NVARCHAR(12)) + '', 'SELECT 0, t' + CAST(@RowID AS NVARCHAR(12)) + '.' + QUOTENAME(@Column) + '')
+ ' WHILE @@ROWCOUNT > 0
BEGIN
SET@Lvl = @Lvl + 1
INSERT@Stage (Lvl, RowKey)
SELECT@Lvl,
t.' + QUOTENAME(@pkColumn) + '
FROM' + QUOTENAME(@Catalog) + '.' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ' AS t
INNER JOIN@Stage AS s ON s.RowKey = t.' + QUOTENAME(@Column) + '
AND s.Lvl = @Lvl - 1
LEFT JOIN@Stage AS cr ON cr.RowKey = t.' + QUOTENAME(@pkColumn) + '
WHEREcr.RowKey IS NULL
END
SELECT ''DELETE FROM ' + QUOTENAME(@Catalog) + '.' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ' WHERE ' + QUOTENAME(@pkColumn) + ' = '' + QUOTENAME(RowKey, '''''''')
FROM @Stage
WHERE RowID > 0
ORDER BY RowID DESC'
INSERT@Unwind
(
RowID,
[SQL]
)
VALUES(
@RowID,
@RowSQL
)
END
END
INSERT@Return
(
RowID,
IsSelfJoin,
HasPk,
[SQL]
)
SELECTs.ID,
s.IsSelfJoin,
s.HasPk,
CASE
WHEN u.RowID IS NULL THEN s.[SQL]
ELSE u.[SQL]
END
FROM@SQL AS s
LEFT JOIN@Unwind AS u ON u.RowID = s.RowID
ORDER BYs.ID,
u.StepID
RETURN
ENDE 12°55'05.25"
N 56°04'39.16"
I have 2 tables A, B with 1 to many relationship
Table A(ProductID), TableB(ProductID, FileID)
I need to find only the records in Table A that may have more than one FileIDs in Table B, since some ProductIDS have multiple FileIDs in Table B...
How can i find the users of the table who updated it last (or) Owner of the table not the schema
View 4 Replies View Related