SQL Server 2012 :: Removing Database Name Prefix From Tables

Aug 21, 2014

When I create a table in sql server database name appears in prefix of table.

My database name: Digitall

My table name: Digitall.News

How can I remove Digitall prefix from my tables?

View 1 Replies


ADVERTISEMENT

SQL Server 2012 :: Removing / Deleting Data From 2 Tables

Mar 11, 2015

I have found a bunch of duplicate records in our housing database that ideally I need to delete.There are two tables that I need to remove data from ih_cml_log_entry and ih_cml_log_notes. There is no unique identifier between the tables for a log entry. So I have had to join on the person_ref, log_seq and the date/time of entry.How do I go about deleting the data - I've used the script below to identify what I need to delete -

SELECT *
FROM
(
select cml.person_ref, cml.open_date + open_time as 'datetime',cml.open_user,cml.log_type
,ROW_NUMBER() OVER (PARTITION BY cml.person_ref, cml.open_date + cml.open_time,cml.open_user,cml.log_type ORDER BY (SELECT 0)) AS RowNo
,n.note
FROM ih_cml_log_entry cml

[code]...

View 2 Replies View Related

SQL Tools :: Adding / Removing Tables In SMSS 2016 - Database Designer Error

Oct 2, 2015

When adding/removing tables in SSMS 2016 CTP2, I keep tripping into:

Index was outside the bounds of the Array

When editing a SQL Server 2014 Database.

If I use the SSMS for SQL 2014, the error is not happening. Possibly a reintroduced bug. Tried to use the Connect site to submit, but it says I am not enabled to use this area.

View 3 Replies View Related

SQL 2012 :: Log Shipping - File Prefix

May 31, 2015

Am just on the lookout to find out whether there is a way to put a prefix at the beginning of the transaction log backup file that is created by SQL Server logshipping.

So, instead of having <DatabaseName>_<DateTime>.BAK (which is what SQL does), I would like to have TL_<DatabaseName>_<DateTime>.BAK.

I have looked into a parameter for the sqllogship.exe command, but cannot find any & have looked at fields in the logshipping tables, & can't see anything there either.

View 0 Replies View Related

SQL Server 2012 :: Getting Results From All Tables In Database

Mar 13, 2014

below is my statement to get data from one column (VARCHAR) from table SUPPLY_ITEM_01

SELECT
@@SERVERNAME as ServerName,DB_NAME() AS DatabaseName,
SUM(CASE WHEN CHARINDEX (CHAR(013) , supplydetail) > 0 THEN 1 ELSE 0 END) AS TotalCHAR013,
SUM(CASE WHEN CHARINDEX (CHAR(012), supplydetail ) >0 THEN 1 ELSE 0 END)AS TotalCHAR012,
SUM(CASE WHEN CHARINDEX (CHAR(010), supplydetail ) >0 THEN 1 ELSE 0 END) AS TotalCHAR010,
SUM(CASE WHEN CHARINDEX (CHAR(009),supplydetail ) >0 THEN 1 ELSE 0 END) AS TotalCHAR009
FROM

[code]...

I need to get result from all the tables and all the columns which has bad data including schemaName, table name and column name in result.

View 9 Replies View Related

Dropping The Prefix When Querying Tables

Apr 18, 2006

Hi, while I'm trying to do Select statements, is there a way/setting whereby I don't have to constantly prefix the table names with a prefix?
For example, I'm working in multiple environments and they're differentiated by the prefix in their table names, uat.** vs prod.**
Any help would be appreciated.

Please email me at dickson.lau@rogers.com

Thanks again.
Dickson

View 15 Replies View Related

Querying Tables Without Schema Prefix

Dec 27, 2006

Hi all,

I'm using SQL Server 2005 Express Edition, and I have restored a database that was using SQL Server 2000 previous. All of that went fine - sprocs and tables/data all made it.

However, when viewing the tables in the object explorer in the management studio, all the tables are prefixed with the schema name (testuser - so the table names are like: testuser.pn_user). Now, all of the code I have queries the tablename only without prefixing testuser to it - but this doesn't work anymore (invalid object name.)

I've tested, and if I prefix the table names in my code with "testuser.", it starts working again, but would rather avoid doing this. Is there something I can do within Sql Server? I've made the "testuser" account "mapped" to the database I'm using (not sure what that means - I'm more of a software developer than a database guy.) Also, when I do "Properties" for the "testuser," the default database is set to the one I'm trying to query w/o the schema.

Any ideas?

View 4 Replies View Related

SQL Server 2012 :: Get Table Stats On All Tables In All Database?

Oct 17, 2014

I have this t-sql code which will get some table stats on one database at a time, I was wondering how I would get it to loop through all databases so it will pull the stats from all tables in all databases. Here is my code:

Select object_schema_name(UStat.object_id)
+ '.' + object_name(UStat.object_id) As [Object Name]
,Case
When Sum(User_Updates + User_Seeks + User_Scans + User_Lookups) = 0 Then Null
Else Cast(Sum(User_Seeks + User_Scans + User_Lookups) As Decimal)
/ Cast(Sum(User_Updates

[code]....

View 1 Replies View Related

SQL Server 2012 :: Removing Character (Letters Only) From A String

Dec 19, 2014

I need to build a query, or use a function to removing alfabetic Character from a string.

My string is
WI14000323-0003

The value i want to keep is : 14000323-0003.

I try using this SELECT:

SELECT STUFF(Upper(z.lote), PATINDEX('%[A-Z]%', Upper(z.lote)), 2, '') from mytable

but in this case i have only 2 letters WI, but in the future i dont know if is only 2 letters, 1 letter or more, then is not what i pretend.

View 2 Replies View Related

SQL Server 2012 :: Removing Greek Characters From A String

Sep 17, 2015

I have a varchar field which contains some Greek characters (α, β, γ, etc...) among the regular Latin characters. I need to replace these characters with a word (alpha, beta, gamma etc...). When I try to do this, I find that it is also replacing some of the Latin characters.

DECLARE @Letters TABLE (Letter NVARCHAR(10))
INSERT INTO @Letters VALUES ('a'), ('A'), ('b'), ('B'), ('α')
SELECTLetter, REPLACE(Letter,'α','alpha')
FROM@Letters

In this case, the "α" is being replaced, but so are "a" and "A".

I have tried changing the datatype from varchar to nvarchar and also changing the collation.

View 4 Replies View Related

SQL Server 2012 :: Removing Duplicate Character From String

Oct 18, 2015

How to write a function to remove duplicated characters only if they come in sequence.

Examples

darrk should return dark

eeagle should return eagle

redd should return red

corner should corner as it is as the r's are not in sequence.

View 9 Replies View Related

SQL Server 2012 :: Getting All Fields From All Tables In Dynamics CRM 2011 Database?

Nov 5, 2014

Doing a data migration from one CRM to another and need to get a listing of all entities in current CRM, together with fields and field types. OK, I got the XRMToolBox which gives me that, but I'm hoping there is a sql tool out there that will do the same, plus give me a count on each field of the number of entries in that field in the database.

View 0 Replies View Related

SQL Server 2012 :: Query To Return Top 10 Tables On Each Database On Server?

Nov 21, 2013

i need a query to return the top 10 tables in each database on a server. have used EXEC sp_msforeachtable 'sp_spaceused ''?''' which returns what I need for one db but I need it to loop through and gather the info for all dbs on server. maybe need cursor not sure. for reporting reasons i would like to include the name of the server and name of database.

View 3 Replies View Related

SQL Server 2012 :: Removing Cursor In Table Valued Function

Oct 16, 2015

I need removing cursor in my table valued function with alternate code.

ALTER FUNCTION [dbo].[eufn_e5_eSM_SE_GetCurrentContentForContainer]
(
@containerSqlId SMALLINT,
@containerIncId INT
)
RETURNS @Results TABLE

[Code] ....

View 2 Replies View Related

SQL Server 2012 :: Count From Multiple Tables For Same Column Exists In Database?

May 19, 2014

i have database which has 25 tables. all tables have productid column. i need to find total records for product id = 20003 from all the tables in database.

View 9 Replies View Related

SQL Server 2012 :: Find Common Link Between Two Tables In Database Without Key Constraints?

Jul 17, 2015

CREATE TABLE [Table 1] (
Id varchar,
TableTwoId varchar,
OtherData varchar )
CREATE TABLE [Table 2] (
Id varchar,
MoreData varchar )

What is the link between these two tables?

I have databases that have zero keys defined, no foreign key constraints, no unique value constraints. I cannot assume that an Identity column is the Id. The end game is be able to output that [Table 1].[TableTwoId] = [Table 2].[Id] but cannot assume that all linkage in the database will be as simple as saying "if the field name contains a table name + Id then it is the Id in that table."

Currently have written a script that will cycle through all columns and start identifying keys in singular tables based on the distinctness of the values in each column (i.e. Column1 is 99% distinct so is the unique value). It will also combine columns if the linkage is the combination of one or more columns from Table 1 = the same number of columns in Table 2. This takes a long time, and it somewhat unreliable as IDENTITY columns may seem to relate to one another when they don't.

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

Will Removing A Registered Server Running SQL Server Remove The Database Too ?

Feb 17, 2006

Hi all, i encountered this problem.. whenever i tried to create publications it always prompt me this error msg "sql server replications does not support nicknames,such "." or "(local)". i know i need to delete the registration and register a new server with a different name.. i just want to double confirm whether deleting the registration will affect the databases or not ? :

View 1 Replies View Related

SQL 2012 :: Extract All Tables Names And Their Row Counts From Linked Server Tables

Oct 7, 2015

I am using the following select statement to get the row count from SQL linked server table.

SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')

MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?

View 1 Replies View Related

SQL 2012 :: Normalization Database Tables

Jan 15, 2015

I have decided to use Wufoo online forms so collect evaluation data from my clients. Each form will have different type of data.

For example

Form 1 might have:
Client id, name, user id, comments

Form 2 might have:
Client id, name, user id, address, comments, telephone number

Form 3 might have:
Client id, name, user id, comments, date of birth, email address

Each form can have over 20+ data types which can be all different to other forms.My question is: What is the best way to store all the data into sql server without creating new columns of new data types everytime a new form is created in wufoo.

View 1 Replies View Related

SQL 2012 :: Removing DB File

Sep 23, 2014

Getting below error while ran EMPTYFILE.

DBCC SHRINKFILE: Page 10:6521 could not be moved because it is a work table page.
Msg 2555, Level 16, State 1, Line 1

Cannot move all contents of file "tempdata" to other places to complete the emptyfile operation.

View 3 Replies View Related

SQL 2012 :: Replication To A Database With More Tables Than Source

Apr 14, 2015

We need to replicate data from a live database to a reporting database nightly.

There is data which is only needed for the reporting function - namely the selection criteria for reports which are to be run nightly or monthly.

Is it possible to have extra tables in the target database, holding the selection criteria?

If not, we'll have the reports running heterogeneous queries between the target database and a separate database with the selection criteria in it.

View 1 Replies View Related

SQL 2012 :: Removing Columns That Are Filtered?

Aug 5, 2015

I have a query that I'm filtering using Customer ID, CustomerID = '12345', even though I need the query to filter that data, I don't need to see that column in my results. I tried removing it from my Select Distinct group but I'm guessing it needs to be there or the filter won't work(like I said, very green). Is there something that I can add to hide this column?

SELECT DISTINCT
RG.ResNumber,
ResWithSupp.SupplierID,
ResWithSupp.ServiceType,
RG.CustomerID,

[code]......

View 4 Replies View Related

SQL 2012 :: Import Visual FoxPro Tables Into Database

Aug 7, 2014

How can I add the option to import data from Visual FoxPro tables into SQL 2012 database?

View 0 Replies View Related

SQL 2012 :: Unable To Export Tables From GIS Application To The Database

Oct 1, 2014

I am using SQL server 2012. An user tried to export tables from GIS application to the SQL database.

After export, and login to the SQL server, we see all the tables has his name as the schema but not dbo.

He was added as a login and user as in a windows group. Meaning he is a member of the windows group.

I assume when export, the default schema should be dbo. but apparently not.
I went to the setting and explicitly make the default schema for the windows group user to dbo. But he tried again, it still use his username as schema prefix to the tables. just wondering why is this?

View 7 Replies View Related

SQL 2012 :: Revoke SELECT On All Tables For All Users In A Database

Nov 26, 2014

I am trying to clean up security. When I check tables in a specific database I see a list of users with select access. There are 1000+ tables in the database. I know I can do 'revoke select on table_name to user_name' ....

View 3 Replies View Related

SQL 2012 :: Possible To Find All Tables That Has A Join With Given Table In Database

Jan 7, 2015

I need to find all tables which has a join (either inside an sp, view, etc) with my given table in a db.

sys.dm_sql_referencing_entities doesn't work here.

Note: i dont want to identify by FK References, for 2 reasons:

1) tables might not met with a join (just FK was defined)
2) sometimes, a join happened between tables, without an FK defined

View 1 Replies View Related

SQL 2012 :: Tables Removed From Diagram After Database Restore

Jun 9, 2015

In last week my database was crashed and some how i managed to restore it back on SQL2K12 but after restoration all the relationships are removed and sql server is showing below message when i open diagram of the database.Table(s) were removed from the diagram because privileges were removed to these table(s) or the table(s) were dropped.how to get back all the relationships of the tables.

View 2 Replies View Related

SQL 2012 :: Join Removing NULLS That Need To Be Displayed?

Apr 22, 2014

I have a table named item that looks like this:

PartNumberDescriptionI.DateCreated
1A1XXX ALPHA1 1/1/2014
2A2XXX ALPHA2 1/5/2014
3A3XXX ALPHA3 1/12/2014
1B1XXX BRAVO1 1/22/2014
2B2XXX BRAVO2 1/28/2014
3B3XXX BRAVO3 2/2/2014

And a second table named orders that looks like this:

OrderNoPartNumberQtyO.DateCreated
1CHXX1 1A1XXX 2 1/8/2014
1CHXX2 2A2XXX 3 1/8/2014
1CHXX3 2A2XXX 1 1/20/2014
2CHXX1 1B1XXX 4 2/10/2014
2CHXX2 2B2XXX 2 2/10/2014
2CHXX3 2B2XXX 1 2/22/2014

I need to pull all records from the Item table and then I need to populate the most recent OrderNo and O.DateCreated. I got this far but if there is a part in the item table that does not have an order against it, I do not get a value and my goal is to see any parts that have not been ordered in the last year. Something like this:

SELECT I.PartNumber, I.Description, I.DateCreated
FROM item I
CROSS APPLY (SELECT TOP 1 O.OrderNo, O.DateCreated
FROM Orders O
WHERE O.PartNumber = I.PartNumber ORDER BY O.DateCreated DESC) O
PartNumberOrderNoO.DateCreated
1A1XXX 1CHXX1 1/8/2014
2A2XXX 1CHXX3 1/20/2014
3A3XXX NULL NULL
1B1XXX 2CHXX1 2/10/2014
2B2XXX 2CHXX3 2/22/2014
3B3XXX NULL NULL

View 4 Replies View Related

SQL 2012 :: Removing Joins And Execution Time?

Jun 13, 2014

I have a query which contains 12 left outer join. I remove some of the joins that don't have parameters. The result is coming same but usually when we remove joining it should take less exec time but for me it is taking more time. What could be the reason?

I am getting same result set.

View 6 Replies View Related

SQL 2012 :: Removing Additional Unwanted Log File?

Jul 6, 2015

I wanted to remove an extra transaction log file that was no longer required, and ran the following against the database...

DBCC Shrinkfile (DB_Name_log2, Emptyfile);
go
alter database [Db_Name]
remove file DB_Name_log2;
go

I got a successful removal message. But if I go into the properties of the database, and click on files, it still shows up. Why is this and how can I get rid of it?

It shows up in sys.master_files as offline.

View 4 Replies View Related

Removing 0 And Copying Fields To Other Tables

May 19, 2008

Hi guys,

please help.

I use to copy one column from one database to another database. What query should I use?

moreover,

How can I convert this

code
000001

the result would be
code
1

thanks!

View 5 Replies View Related

Removing System Replication Tables.

Jul 23, 2005

Hi, i restored a backup from a database thas has replication configured.When i restored it the system tables that the merge replication createsare restored too. I was investigating on internet and I found that i candelete it using this query:sp_configure 'allow updates', 1goreconfigure with overridegoDROP TABLE aonflict_SiacDataEEC_security_info...sp_configure 'allow updates', 0goreconfigure with overridegoDo somebody know if i use this queries to delete this tables i candamage the database or is correct to use it.Thanks a lot for your help.*** Sent via Developersdex http://www.developersdex.com ***

View 1 Replies View Related







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