SQL Server 2012 :: Removal Of Records Dynamically Using Information Schema
Aug 14, 2015
I'm looking to dynamically remove records from tables dynamically using the information schema within SQL Server. Looking to remove records from all the tables within a schema. I have gotten as far as generating the script dynamically then using a while exist clause to execute the delete statements.
DECLARE@TargetSchema varchar(100),
--@LibNameData varchar(100),
@fnameIndex varchar(100),
--@startOFR_SCR_FILENAME_DATE varchar(25),
[code]...
Would like to execute the statements generated by the results from the information schema.
View 6 Replies
ADVERTISEMENT
Sep 11, 2015
I've done already the schemas for all the tables a you suggested.But when I created a new table the dbo prefix (dbo.ADM.NewTable) appears.I'd like this dbo not to automatically appear whenever i created a new table — as it requires me to alter whenever I created a new table.Is this something that is required to determine the creator of the table?Otherwise, I'd like that to remove.
View 7 Replies
View Related
Sep 5, 2007
I know I've done this before and I can't find, and it's kinda of foggy here today...
anyone see anything with this
EXEC sp_addlinkedserver
'PAERSCBVD0045',
N'SQL Server'
Connect as X002548 since logins are on both serves
*/
-- Tables in 14 not in 45
SELECT S14.TABLE_NAME
FROM INFORMATION_SCHEMA.Tables S14
LEFT JOIN INFORMATION_SCHEMA.PAERSCBVD0045.dbo.MEP.Tables S45
ON S14.TABLE_SCHEMA = S45.TABLE_SCHEMA
AND S14.TABLE_NAME = S45.TABLE_NAME
WHERE S45.TABLE_SCHEMA = IS NULL
AND S45.TABLE_NAME = IS NULL
View 5 Replies
View Related
Sep 6, 2007
Information Schema is the part of the SQL-92 standard which exposes the metadata of the database. I have written a small article that exposes the same. Let me know your comments on the same. http://aspalliance.com/1380_Information_Schema_and_SQL_Server_2005
Uday Denduluri
Software Engineer
Refer my articles at http://aspalliance.com/author.aspx?uId=62740
View 5 Replies
View Related
Nov 15, 2015
I have this table:
id | Name | Age
==================
1 | AAA | 22
1 | AAA | 22
2 | BBB | 33
2 | BBB | 33
2 | BBB | 33
3 | CCC | 44
4 | DDD | 55
I need to delete from this table all the duplicate records and leave only one record. The table will looks like this:
id | Name | Age
==================
1 | AAA | 22
2 | BBB | 33
3 | CCC | 44
4 | DDD | 55
I work with sqlCE for Mobile...
View 8 Replies
View Related
Apr 29, 2015
I currently have a lot of XML blobs being stored in several of the production databases that I look after. They're there as a application "logging" feature, so that the developers can investigate issues if they ever occur.
I want to get these out of my OLTP databases asap and am currently looking at options. I know Brent Ozar recently posted an article about Content Addressable Storage systems [URL] ....
How do you handle point in time recovery? Any other ways of storing this data?
View 9 Replies
View Related
Jan 28, 2004
Is there an information schema view that lists all the triggers?
Thanks in advance.
View 6 Replies
View Related
Jan 19, 2004
hi all,
I have a duplicate stored procedure one has proper entries in the information_schema and the other with the same name has null entries in information schema meaning , the specific_schema and routine_schema entries are null and now it doesn't allow me to open the stored proc, neither it allows me to delete the proc.
while deleting it says" procedure_Name not found in the (NULL) collection.
any help will be highly appreciated.
REGARDS,
HARSHAL.
View 1 Replies
View Related
Oct 31, 2005
Hello All,Could someone help provide a query that I can run todetermine the primary key and unique columns for anygiven table ?Thanks in Advance,Akintoye
View 1 Replies
View Related
Jun 30, 2006
We are designing a server where functions and procedures are to be kept in a single database. Our other databases will call these functions. The functions will need to reference tables in the schema being called from. Is it possible to pass a schema name to a function? If so, then how?
I tried passing something like:
CREATE FUNCTION [dbo].[getPaymentReceived2](@inSchema CHAR(30), @inAraccountid CHAR(15), @inInvoicestart SMALLDATETIME)
RETURNS MONEY
AS
BEGIN
DECLARE @outAmt money
DECLARE @arinvoiceid char(15)
SET @arinvoiceid = (SELECT ai.arinvoiceid
FROM @inSchema.dbo.arinvoice ai
WHERE ai.araccountid = @inAraccountid
AND ai.invoicestart = DATEADD(month, - 1, @inInvoicestart))
I get an incorrrect syntax error.
View 6 Replies
View Related
Aug 12, 2015
I am working on moving information from one of our databases into a newer one the company has recently setup. I am working in MS SQL Server 2012 and am trying to Inset/Update (depending on the information) a part table. I will list the two tables I am working on as Old (where I am getting the information) and New (where it is to be inserted into).
The old table has about 250 columns of information, most of which is not needed. The needed information is as follows: Name, ID, Component1, Component1_PIN, Component1_SN, Component1_Description, Component2, Component2_PIN, Component2_SN. The component section repeats up to Component12.
The new table has columns setup as such: Name, ID, Case, CasePIN, CaseSN, Case_Desc, Processor, ProcessorPIN, ProcessorSN, Processor_Description, Memory, MemoryPIN, MemorySN, Memory_Description, etc.
The issue I am having is that in the old table each component can have Case, Processor, Memory, etc in that one column. I need to insert Case, Memory, etc into the correct column in the new table while keeping intact the rest of the information.
Example:
Old Table
Name | ID | Component1 | Component1_PIN | Component1_SN | Component1_Description | Component2 | Component2_PIN | Component2_SN | Component2_Description
Nest8 | 5682 | Case | 901834 | 237848117 | Black, rectangular | Memory | 9081234 | 5398798134 | NULL
Nest8 | 5978 | Case | 901463 | 237848138 | Black, rectangular | Processor | 2394875 | 2903857809 | Bad
Reds3 | 5683 | Memory | 2405 | 89752342 | Crucial | HardDrive | 92387595 | 457982234 | NULL
Bass | 5644 | HardDrive | 79872346 | 5321675789 | NULL | Case | 10984528 | 3498769872 | NULL
I am not sure how to loop through and grab each part and place it in the column it needs to be while keeping it with the ID.
View 4 Replies
View Related
May 29, 2015
Whenever SQL Server get restarted, tempdb gets recreated with its last configuration.
let me know where SQL Server store tempdb configurations? How does it know how many Tempdb files it needs to create on restart?
View 2 Replies
View Related
Jul 16, 2015
I am using following sql to extract locking information in database. It only work on current selected database, how can I tune to work on all databases and not only currently selected?
SELECT DISTINCT
ES.login_name AS LoginName,
L.request_session_id AS BlockedBy_SPID,
DATEDIFF(second,At.Transaction_begin_time, GETDATE()) AS Duration_Sec,
DB_NAME(L.resource_database_id) AS DatabaseName,
[Code] ....
View 3 Replies
View Related
Nov 27, 2014
Need to scheduled to copy table data from one database to another database daily @7:00 AM by creating the table with date and time stamp.
Example :
Test1DB ---> Table1
Test2DB -----> Table1_281120140700 in Day1 and Table2_291120140700 in Day2.....and so.
SELECT * INTO Test2db.dbo.new_table_name FROM Test1db.dbo.old_table_name in this format ??
script and scheduled to run daily @ 7:00AM?
View 1 Replies
View Related
Sep 10, 2015
Till recently we were using the following code to retreive schema information of columns of tables
Dim schemaTable = connection.GetOleDbSchemaTable( _
System.Data.OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, tableName, Nothing})
Now instead of getting the name of table (which i was using as param for filtering) i'm going to receive a sql-query. Now my question is if I were to get a query like the following :
SELECT
[EmployeeID],
[Title] + ' ' + [LastName] + ' ' + [FirstName] AS FullName,
[BirthDate],
[Address],
[City] + ', ' + [Region] + ', ' + [Country] + ' - ' + [PostalCode] AS FullAddress
FROM [dbo].[Employees]
Then how can I retrieve the schema information of only the columns present in the query.
(Its possible that i might get a query with multiple tables with joints)...
View 3 Replies
View Related
Dec 1, 2014
I am using SSIS package for pulling the data(last 2 months data).
Since the data size is huge, i have to split the data into hourly basis and pull the data.
how i can make this dynamic? Right now i am changing the hours manually after package execution.
View 9 Replies
View Related
Dec 5, 2014
Is it possible to dynamically grow a data file for TempDB?
I have some TSQL that I want to execute (this is raw tsql, not something I'd typically share) when I get an alert that TempDB is growing.
SET NOCOUNT ON;
DECLARE @TempDB TABLE (FileID tinyint, name varchar(50), size bigint)
DECLARE @TotalSize decimal(19,4), @TotalFiles int, @CurrentTempDBSize bigint, @SQL varchar(1000), @DBName varchar(50)
SET @SQL = ''
INSERT INTO @TempDB
[code]....
ENDThe code runs fine, but doesn't actually increase the size of the file.
View 7 Replies
View Related
Apr 20, 2015
I am having 2 tables one is staging temp and another is main import table.
In my staging table there are 3 column Col001,Id,Loaddate
in Col001 column data are present with '¯' delemeter.
I am having function which is used to load data from staging to import table using one function.
this function create a insert statement.
My Existing function
-- Description: To Split a Delimited field by a Delimiter
ALTER FUNCTION [dbo].[ufn_SplitFieldByDelimiter]
(
@fieldname varchar(max)
,@delimiter varchar(max)
,@delimiter_count int
[Code] ....
I am unable to get correct statement with above function.
View 1 Replies
View Related
Oct 13, 2014
I have a group of about 5 servers (which will likely grow toabout 25 in the near future) with their names listed in a table in a database on one of the servers. I want to query all servers in that table using the following query to pull the storage drive, database name, created date, age and size of the databases for each server listed in the table:
SELECT left(mf.Physical_Name,2) AS Storage_Drive,
DB_NAME(mf.database_id) AS DatabaseName,
db.create_Date,
DateDiff(day, db.create_date, getDate()) Age,
sum((mf.size*8))/1024 SizeMB
[Code] ...
How would I best accomplish this if I want to implement it using a TSQL procedure?
View 4 Replies
View Related
Oct 24, 2015
missing witness server information and the fail-over is broken suddenly? 4:00am no maintenance job. I have one sql job on 10pm for backup on database transaction log only.
I can see the primary have problem then perform fail-over to mirror database, the auto fail-over was broken.
I re-build the sql mirror is OK , but i want to find the root cause.
Windows application event was full when there have many failed event, i have increase log size for application event.
View 7 Replies
View Related
Oct 1, 2014
I am tasked with truncating and reloading tables from one server to another. Company policy prevents cross-server queries, but allows SSIS packages with cross-server connections. I am doing this for about 25 tables. I have the table names in a single table & I have created an FEL to execute tasks against each table one-by-one. It works fine to truncate all the tables. I run into issues, though, with the DataFlowTask. I'm able to tell it which server & table to dynamically connect from and to, but it doesn't know how to map the metadata. They're the exact same columns and field names in both source & destination.
View 9 Replies
View Related
Feb 5, 2015
After performing a join operation on two tables i get the below resultset
pid, fname, typename, pname, pcost
1, cad, bars, product-1, 100
2, har, witte, product-2, 120
3, nes, bars, product-3, 119
Now i need to create files with the obtained resultset like
Column 'fname' is the folder name and 'typename' should be the file in the particular folder.
For example the first record should be inserted into file name 'bars.txt' in the folder 'cad' and third record should be created in file name 'bars.txt' in the folder 'nes'.
View 2 Replies
View Related
Apr 19, 2015
I am having 100 of flat files need to load in respective staging table.I want to create table on run time as per filename input.suppose if input filename is ABC then table name should be Staging_ABC if file name is XYZ then it should be Staging_XYZ.Table structure is below need to create at run time
CREATE TABLE Staging_'Filename'(
[COL001] [varchar](4000) NULL,
[Id] [int] IDENTITY(1,1) NOT NULL,
[LoadDate] [datetime] NOT NULL default getdate()
)
View 2 Replies
View Related
Apr 19, 2015
I am getting error when I am trying to create table on runtime
Declare @FileName varchar(100)
Declare @File varchar(100)
set @FileName='brkrte_121227102828'
SET @File = SUBSTRING(@FileName,1,CHARINDEX('_',@FileName)-1)
--=select @File
[Code] ....
Error massage:-
Msg 203, Level 16, State 2, Line 16
The name 'CREATE TABLE DataStaging.dbo.Staging_brkrte ( [COL001] VARCHAR (4000) NOT NULL, [Id] Int Identity(1,1), [LoadDate] datetime default getdate() )' is not a valid identifier.
How to resolve above error....
View 4 Replies
View Related
Sep 19, 2014
In SQL Server 2012 (also 2005 and 2008), can you have Referential Integrity across a Database? Across a Schema?
View 1 Replies
View Related
Oct 24, 2014
I need to copy all objects (views, procedures, tables, functions, etc -- no data) that are under a specific Schema in a database, but the Generate Script option in SSMS doesn't give me the option to do this. How I can generate a script that's Schema specific?
View 2 Replies
View Related
Dec 2, 2013
I have created some dynamic sql to check a temporary table that is created on the fly for any columns that do contain data. If they do the column name is added to a dynamic sql, if not they are excluded. This looks like:
If (select sum(Case when [Sat] is null then 0 else 1 end) from #TABLE) >= 1 begin set @OIL_BULK = @OIL_BULK + '[Sat]' +',' END However, I am currently running this on over 230 columns and large tables 1.3 mil rows and it is quite slow. How I can dynamically create a sql script that only selects the columns in the table where there is data in a speedier manner. Unfortunately it has to be on the fly because the temporary table is created on the fly.
View 1 Replies
View Related
Mar 20, 2014
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
View 0 Replies
View Related
May 21, 2015
I am working with one of the production database around 200 GB. This database has above 350 tables and more than 500 Indexes. I am feeling the database has so many Indexes than the required ones
When I run the below query, it gives me some indexes read value "0". The server was restarted a month ago.Is it ok to remove those indexes?
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] ,
i.name AS [Index Name] ,
i.index_id ,
user_updates AS [Total Writes] ,
user_seeks + user_scans + user_lookups AS [Total Reads] ,
user_updates - ( user_seeks + user_scans + user_lookups )
AS [Difference]
[code]....
View 2 Replies
View Related
Dec 5, 2000
I have a SQL database (10000 records / 50 users) that stores information on
faxes that we receive. This will sound odd but the last few days some weird
things have happened. Chunks of data change arbitrarily - no script that
I have can make these
changes. Couple examples:
1) the number of pages will change from 1 to 3 - not every entry just a
group and usuall ones that have a particular status
2) the status will change from CTW to PFR - not every CTW will change just
a chunk
There appears to be no pattern to these changes.
Again, no script that I have can make these changes. This happened 6 months
ago and I reinstalled service packs (NT and SQL), reinstalled data components
and defragged the hard drive. The problem stopped either because of one
of these actions or either on it's own. I have done all of these things
this time but nothing has worked. Anyone have any ideas?? Any help is appreciated.
View 1 Replies
View Related
Nov 21, 2007
Hi,
I am trying to combine information from two or more records into one and I am completely stuck on a solution for my problem so I hope there is someone out there who can help me.
My table looks like this:
ID - DayNr - Transportation - TransOrder - Route
25 - 1 - Car - 1 - Text A
25 - 1 - Train - 1 - Text B
25 - 1 - Train - 2 - Text C
25 - 7 - Train - 1 - Text D
25 - 7 - Train - 2 - Text E
I want to combine all Route - information belonging to the same combination of ID & DayNr & Transportation into one new record. The result should look like:
Column 1 - Column 2
25/1/Car - Text A
25/1/Train - TextB;TextC
25/7/Train - TextD;TextE
I have tried Coalesce-statements and Cursor-solutions but until now everything I tried didn't work. Ideas anyone?
Thanks.
RMG
P.S. ID is not my primary key and doesn't have to be unique
View 14 Replies
View Related
Nov 21, 2007
Hi,
I am trying to combine information from two or more records into one and I am completely stuck on a solution for my problem so I hope there is someone out there who can help me.
My table looks like this:
ID - DayNr - Transportation - TransOrder - Route
25 - 1 - Car - 1 - Text A
25 - 1 - Train - 1 - Text B
25 - 1 - Train - 2 - Text C
25 - 7 - Train - 1 - Text D
25 - 7 - Train - 2 - Text E
I want to combine all Route - information belonging to the same combination of ID & DayNr & Transportation into one new record. The result should look like:
Column 1 - Column 2
25/1/Car - Text A
25/1/Train - TextB;TextC
25/7/Train - TextD;TextE
I have tried Coalesce-statements and Cursor-solutions but until now everything I tried didn't work. The big issue here is that I have to base my concatenation on 3 columns. Ideas anyone?
Thanks.
RMG
P.S. ID is not my primary key and doesn't have to be unique
View 1 Replies
View Related
Sep 13, 2005
I’ve got a situation where the columns in a table we’re grabbing from a source database keep changing as we need more information from that database. As new columns are added to the source table, I would like to dynamically look for those new columns and add them to our local database’s schema if new ones exist. We’re dropping and creating our target db table each time right now based on a pre-defined known schema, but what we really want is to drop and recreate it based on a dynamic schema, and then import all of the records from the source table to ours.It looks like a starting point might be EXEC sp_columns_rowset 'tablename' and then creating some kind of dynamic SQL statement based on that. However, I'm hoping someone might have a resource that already handles this that they might be able to steer me towards.Sincerely,
Bryan Ax
View 9 Replies
View Related
