DB Design - Alphabetical Column Names?
Dec 13, 2004
I have been discussing with some coworkers whether or not it makes sense to invest the time to alphabetize the column names in our tables (aside from the PK and possibly FK's that could be listed first). My reasoning for doing so would make it much easier to scan the list of columns in a table that I was not familiar with to see if it contained a particular column (i.e. meeting_id). I was just wondering if this is common at all in our industry for new DB design (I realize why legacy systems would not be ordered in this way). I remember seeing MSFT designed their tables this way when Site Server first came out.
An argument was made that when you add a new column you would insert it into the appropriate location and in order to make this happen, EP needs to create a temp table, move the data and consequently lock the table until the data has been transferred. While this is correct, I do not see this negative as outweighing the positive experience achieved by scanning a list of fields in a table in a more orderly fashion.
Thoughts?
View 14 Replies
ADVERTISEMENT
Aug 5, 2015
I am trying a create views that would join 2 tables:
Table 1: Has all the columns need by a view (
Name: Product
Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc
Table 2: Is a lookup table that provides the names of columns
Name: lookupTable
Structure: tableName, ColumnName, columnValue
Values: Product, Attribute1, Color
Product, Attribute2, Size
Product, Attribute3, Flavor
Product, Attribute4, Shape
I want to create a view that looks like
ID, Color, Size, Flavor, Shape
View 4 Replies
View Related
Nov 26, 2007
Hi, I have a table whose Identifying column is not an integer but rather a manually entered id. (i.e. 106F, 106-09, G11 etc.) When sorted ascending, a G11 will come before a G2 in the list, 106-11 before 106-2, etc.I would like to insert a new column in the database or use some kind of function when sorting the database to ensure that the list in returned in the proper order. Any ideas?Much appreciated...
View 16 Replies
View Related
Mar 12, 2007
Hello
I know how I can display a list of names in alphebetical order on my website:
Select L as [Last Name]
From Name_CatEWhere Education = 'yes'Order ByLName ASC
However, to make things a little more orginised I would like to view my database table column in alphabetical order also, but ithie code does not work within my database.
What do I need to change in the following code, to view my database table column in a-z order?
SELECT LName FROM Name_CatEORDER BY LName ASC
Thanks
Lynn
View 1 Replies
View Related
Apr 17, 2014
i am working on a small project, that I have found that someone is storing a float as a varchar(). But there are also some actual words in the same column.
I am trying to determine how I can select only the rows with alphabetical characters in that column.
Some of the data is:
1.5008e+015
1.54453e+015
1.51922e+015
1.51922e+015
1.52243e+015
but there is a mix of alphabetical characters in there as well.
1.51922e+015
1.53122e+015
FMCIT
ABCNP
FMCPNG
1.62073e+015
1.6127e+015
I want to be able to select the rows with only the alphabetical characters. There is a huge mix, and I am assuming that every first letter is one of the 26 alphabetical character used. How can I write a query to use a REGEX to select any and all rows that cannot be CAST as a Float? I have nill to no experience using REGEX.
View 9 Replies
View Related
Jan 27, 2015
I am having problem with the unpivot function of sql 2012, i unpivot my column then i get the result that i wanted but the error that i was encountering was the unpivot is automatically sort the column in alphabetically order which is not I desire,
Here is my code
@syear nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
[Code] ....
View 1 Replies
View Related
Jan 22, 2004
Hi
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
Thanks.
View 5 Replies
View Related
Apr 14, 2006
In the past, we had table names such as:
PurchaseOrders
SalesOrders
Now with SQL 2005, you can use schema namespaces, so it could become:
Purchasing.Orders
Sales.Orders
So essentially you can use exactly the SAME table names, in different schemas.
So... would you recommend to change to the last model, or just stay with the old way?
What is best practice?
One of the reasons why I ask this question is that I looked at the AdventureWorks sample database included with SQL Server 2005, and Microsoft is using:
Sales.SalesOrderDetail
Purchasing.PurchaseOrderDetail
etc...
Why not:
Sales.OrderDetail
Purchasing.OrderDetail
etc...
????????
View 1 Replies
View Related
Nov 2, 2015
In our application we are copying data between 2 sqlserver databases using linked server. Say, sqlserver1 is source and sqlserver2 is destination, then, our application will be on sqlserver2 box and will copy data from sqlserver1 to sqlserver2.
User also need to choose from which database of sqlserver1, the data need to be copied. This data is our application data.. Nothing related to sqlserver database.
We are using the following query, to get the database names of the source sqlserver..
SELECT * FROM <linked server to dest db>.master.sys.databases
My questions is : is this query generic enough that works on all sqlserver versions?
Is the master database name of sqlserver configurable or its alwasys fixed as "master"?
View 4 Replies
View Related
Apr 28, 2008
I need to create the following table in reporting services
PRODUCT April March Feb
2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6
My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???
Thanks in advance!!!
View 1 Replies
View Related
Oct 5, 2015
How Can I Create Two Column with Math Function In one Column Like Below.
Create Table Tbl_V_Voucher_Details
(
Id Int IDentity (1,1) Primary Key,
Catid Int Foreign Key References Tbl_V_Voucher_Info(Id),
ItemId Int Foreign Key References Tbl_V_Item(Id),
Discription varchar
(100),
Qty Int,
Price Float,
Qty * Price As Total
)
View 5 Replies
View Related
Mar 6, 2014
there is another way to get the alphabetical value for a number other than using a case statement?
example:
Count = 1 That's = A
Count = 5 That's = E
Count = 8 That's = H
and so on I know a case could do this but wondering if there is a nice function or simple statement that would do this on the fly?
View 2 Replies
View Related
Dec 29, 2003
Hello,
Im trying to get the column names from a database and display them in textboxes. someone has already helped me by tellnig me that i need to use the FillSchema command. Which works just fine and I can see only the colum names in a datagrid when i bind it to that.
The problem is that I do not know how to extract the name of a column and put it in to a textbox ?
does anybody know how I can fo this ?
Thanks a million
Rob
View 3 Replies
View Related
Aug 24, 2005
Hi,How do I display the column names from my Sql server table?In asp3 the recordset allowed thisforeach key in rs ColumnName = key.name ColumnValue = key.valuenextHow do I do this in .Net?I want to use a DataReader so I can read through each record and only display the ones I want.TABLE_ONEColumn_OneColumn_TwoColumn_Threethanks,
View 1 Replies
View Related
Mar 30, 2004
Hi, i need a query to have the columns names !!
If you have another solution, let me know !!
superj !!!
View 12 Replies
View Related
Apr 10, 2008
Hi
When I run this query..
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='TableTest'
I get all the column names, but the result is not in the same order as when I check the design for the table, why is that?
View 2 Replies
View Related
Dec 27, 2005
At my work, we are upgrading a number of MS Access 2000 databases to SQL Server 2000. Many of the columns have names that do not follow the rules for Identifiers. For example there are columns with a numeric names 1,2,3,4,5,6,7,8,9. There are also columns with a hyphen in the name and columns that begin with a number and not an underscore or an alpha character. Plus there are columns with names like first, last, position, etc. (There was also columns with reserved words used as the names. I have changed those column names in the databases that I have already converted) Will leaving these names alone create a problem? The database serves as the backend to ASP pages.
Miranda
View 4 Replies
View Related
Mar 27, 2007
Apologies if this has been done before, but I couldn't find a completed example. If anyone has time, I'd love to see some improvements...
Where's fribble when you need him?
/*
function:
numeric_order
arguments:
@numeric_string - a string of mixed alpha and numeric values
@max_digits- the maximum length of digits to compare
description:
Function numeric_order creates an orderable string based on the "numeric" value of @numeric_string
which can be ordered alphabetically.
Ideally the strings should really be broken up into constituent parts and ordered properly,
but occasionally you come across data where it's just not worth the while.
eg
select title from regulations order by title
returns:
Regulation 1(a) section 3
Regulation 11 section 100(b)
Regulation 11 section 2(c)(iii)
Regulation 2 section 1
Regulation 21 section 3 (b)
whereas
select title from regulations order by dbo.numeric_order(title,10)
returns:
Regulation 1(a) section 3
Regulation 2 section 1
Regulation 11 section 2(c)(iii)
Regulation 11 section 100(b)
Regulation 21 section 3 (b)
which is the order most users would expect.
NOTE: because the original strings are mixed, the user may include alphas between digits which are to be
sorted alphabetically, which means the string must keep all alpha parts of the original string as is.
expected output:
select dbo.numeric_order('Part 1(b) subsection 1',4)
returns
Part 0001(b) subsection 0001
test data:
use test
--go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[regulations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[regulations]
--go
create table regulations (id int identity(1,1), title varchar(200))
--go
insert into regulations (title) select 'Regulation 1(a) section 3'
insert into regulations (title) select 'Regulation 11 section 100(b)'
insert into regulations (title) select 'Regulation 11 section 2(c)(iii)'
insert into regulations (title) select 'Regulation 2 section 1'
insert into regulations (title) select 'Regulation 21 section 3 (b)'
--go
select title as [Incorrectly Ordered] from regulations order by title
--go
select title as [Correctly Ordered] from regulations order by dbo.numeric_order(title, 10)
--go
drop table [dbo].[regulations]
--go
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[numeric_order]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[numeric_order]
go
create function [dbo].[numeric_order](
@numeric_string as varchar(1000),
@max_digits as int)
returns varchar(8000) as
begin
declare @return varchar(8000)
declare @part varchar(1000)
declare @digit_position int
declare @rest varchar(1000)
declare @non_digit_position int
declare @numeric_term varchar(1000)
declare @after varchar(1000)
declare @between varchar(1000)
declare @first_time int
declare @digits varchar(100)
--create a string of zeros equal in length to max number of digits of enclosed numeric values
set @digits = replace(space(@max_digits),' ','0')
--handle length issues
--worst case scenario is a number every second character multiplied by padlen < 8000
--which would potentially add len(@numeric_string)/2 * padlen characters - so subtract this from the string
set @part = left(@numeric_string, ((len(@numeric_string)/2) * @max_digits))
--starting values
set @non_digit_position = 0
set @first_time = 1
set @return = ''
--loop while not at end of string
while ((@non_digit_position > 0 or @first_time > 0) and (len(@part) > 0))
begin
--if there are digits in the string
set @digit_position = patindex('%[0-9]%', @part)
if @digit_position > 0
begin
--get the part of the string after the first digit
set @rest = substring(@part, patindex('%[0-9]%', @part) + 1, len(@part)-patindex('%[0-9]%', @part) + 1)
set @non_digit_position = patindex('%[^0-9]%',@rest)
--extract the string of digits
set @numeric_term = case
when @non_digit_position > 0 then substring(@part, @digit_position, @non_digit_position)
else substring(@part, @digit_position, len(@part) - @digit_position + 1)
end
--keep track of the rest of the string after and between the digits
set @after = substring(@part, @digit_position + len(@numeric_term), len(@part) - @digit_position + @non_digit_position)
set @between = '' + substring(@part, 1, @digit_position - 1)
--build return string
set @return = @return + @between + right(@digits + @numeric_term, @max_digits)
end
else
begin
--no more digits, just add back the rest of the original string
set @return = @return + @part
set @after = ''
end
--iterate
set @first_time = 0
set @part = @after
end
return @return
end
go
--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
View 9 Replies
View Related
Jan 1, 2008
Hello,I'm still extremely novice to SQL and I've tried googling how to produce this result but I've been encountering a lot dynamic sql commands which isn't exactly what I want. If this is an ignorant question I do apologize but heres my scenario: I have a table with columns of the day, for instance: Monday_hasData (bit)Monday_DataAnd what I want to do is essentially pass in "Monday" as a parameter and rather than doing an If statement on each row, I would like to just like to do something like assign @dayCheck + "_hasData" to a variable and then use @dayCheck as part of my query. Is this possible or am I going to have to have 7 cases? Thank you for any input,Chance
View 2 Replies
View Related
May 30, 2008
Hi all,
By using below query i can get no of tables having the give column name in a particular database,
SELECT COUNT(*) AS CounterFROM syscolumnsWHERE (name = 'empno')
but i want to know the table names too?
any one please suggest me how to find table names too......
In other words i know the particular column name and right now i want to know the table names in which this column name exists.
View 2 Replies
View Related
Jul 12, 2002
Hi,
I have a table in which out of 20 columns, there will be data only in a few columns. So, I need to find those column names which have data in them. I was able to do it using the information_schema and using a cursor to loop through all the column names and find out which columns have data in them. But i need to know if there is a more efficient way of doing this without using the cursor. Can somebody please let me know how this can be done without using a cursor? Thank you.
View 2 Replies
View Related
Nov 26, 2002
Can anyone help me with a SQL statement that will list all the column names in a table please ?
I just want to list out the column name so that i can develop asp/vb more effectively than having to use SQLEM and Design table to see the field names.:confused:
thanks
FatherJack
View 2 Replies
View Related
Jun 10, 2004
Hi is possible to create dynamic column name
example
Declare
@StartDate as dateTime
Select @StartDate = '2004-06-05'
select
SUM(Case When table1_date BETWEEN dateadd(day,-6,@StartDate) and @StartDate then 1 else 0 end)AS [dateadd(day,-6,@StartDate)],
SUM(Case When table1_date BETWEEN dateadd(day,-13,@StartDate) and dateadd(day,-7,@StartDate) then 1 else 0 end)AS [dateadd(day,-13,@StartDate)]
from
table1
View 2 Replies
View Related
Aug 19, 2004
I would like to pass into a stored proc the column names I want it to return; how do I do this please?
So I pass in say:
@p_AccountNumber with a value of 'AccountNum'
and the SELECT that the sp fires is of the form
SELECT AccountNum from AccountTable
Any help appreciated
View 2 Replies
View Related
Jan 19, 2005
thanx for the help in advance,
i have a table with Monday, Tuesday, Wednesday. .... Sunday
i get the weekday by
select @stat = datename(dw, getdate()) which in this case is Wednesday
but when i do something like
select top 2 * from myTable
where 'myTable.'+@stat = 1
which is as same as
select top 2 * from myTable
where myTable.wednesday = 1
i receive a syntax error. how can i dynamically select myTable.Wednesday ?
i also tried using
select top 2 * from Intercon
where '@stat' = '1'
but then this doesn't return anything where it should.
any help?
View 2 Replies
View Related
Mar 7, 2005
Is there a way that you could get the column names for each table in a database using 1 query?
something like:
tbl colname
t1 catID
t1 catName
t2 prodID
t2 prodDesc
t3 cartID
...
...
I know it would be long, but I would just be searching through the saved output for specific names.
View 1 Replies
View Related
Nov 15, 2005
Hi All,
I was wondering how would I get the column names from a table? Not the results just a listing of column names. What is the command to get this information? I am using MS Access, but I posted here because MS SQL is the closest thing on this forum and the syntax is usually similar.
Thanks a bunch.
Val
View 5 Replies
View Related
Oct 13, 2005
SQL server throws an exception when I try to run the following code:
ALTER TABLE contacts ADD default ntext NULL;
The reason for that is that "default" is an SQL keyword.
The way I handle this now is I put an underscore at the front:
ALTER TABLE contacts ADD _default ntext NULL
Is there a cleaner way to handle this, i.e. to keep the column name "default" and forse sql server to create a column with this name?
Thank you!
View 9 Replies
View Related
Jun 22, 2007
Morning guys,
I have a project where Iam suppose to script all columns in every database/table/columns on the network.
I figured the best way to do this would be using sql-dmo (Sql Server 2000)
Does anyone have any suggestions on getting started with this project?
thanks,
Jonathan
View 14 Replies
View Related
Nov 20, 2007
Hello, everyone:
I used bcp to generate a txt file as:
EXEC master..xp_cmdshell 'bcp "SELECT * FROM Test..Orders" queryout "c:/test.txt " -U tester -P tester -c'
It works fine except without column names of table. Does any one have idea that can bring out column names from table?
Thanks
ZYT
View 9 Replies
View Related
Jan 25, 2004
I have been looking in the SQL Server Help Files, but I cant seem to find the syntax to change the name of a column name..?
Can it be done? if so, what is the syntax?
View 4 Replies
View Related
Mar 15, 2004
I am writing a stored procedure in which I have a query that selects the Headings of Columns from another table...
I want to then create a loop that will contain a variable with the value of the column heading and then set the column to a value of NULL...
Is there any way to accomplish this???
I thought about placing these values into a temp table...
This is what I have so far...
Declare @QueryX nvarchar(500)
Declare @FieldName varchar(20)
Create Table #UpdateRejDoc
(
Abbreviation varchar(20)
)
Insert into #UpdateRejDoc
Select Abbreviation
From tbl_Titles
left JOIN tbl_TitleRouting on tbl_Titles.Title_ID = tbl_TitleRouting.Title_ID
Where tbl_TitleRouting.Application_ID = @Application_ID
While Exists (Select Abbreviation from #UpdateRejDoc)
Begin
Set @QueryX = 'Update DBLandfillUser.tbl_ObjectApprovals' +
'@AppName + Set @FieldName = null Where object_id =' + Cast(@object_id as VarChar(20))
End
View 1 Replies
View Related
Apr 22, 2008
Hi All,
Heres my problem...
If I create a table T1 with a couple of columns; MyPKCol, ColA
I then create a view V1 as "Select * From T1"
At this stage, if I run "Select * From V1" the result will, as expected, include following columns:
MyPKCol,ColA
If I now add another column, ColB, to T1 and then again run "Select * From V1", I still get the result with only 2 columns;
MyPKCol,ColA
In order for V1 to return MyPKCol,ColA,ColB I have to drop V1 and then recreate it again.
Can someone explain why this is and how I can clear this type of cache?
I've tried the following commands, but it didn't work:
DBCC FREEPROCCACHE;
DBCC FREEsystemCACHE( 'ALL' );
DBCC DROPCLEANBUFFERS;
There has to be an easier way than having to recreate SPs, Function and Views just because you make a change to a table.
I hope I made sence
Thank you in advance for any help.
roamso
View 8 Replies
View Related
