How To Find Objects That Are Not Following Naming Convention
Apr 1, 2008
Hi All,
I have an assignment to change names of the SPs that are not following Standard naming convention in all databases. For this first I need to find out all the SPs that are not following naming convention. Can anybody help me in finding out the objects that are not following standard naming conventions. It is first step in completing this assignment. If anybody deal with this type of task before please guide me.
Hope any one of you will reply me with more information......
Thanks,
Pradeep
View 9 Replies
ADVERTISEMENT
Aug 21, 2006
Hi all,
I wanted to ask for the naming conventions in SQL SERVER.as in case of pl/sql server we have ..name_of_package.name_of_procedure thats how we call the procedures i.e. owner of schema then name of package n then procedure name.....i want to ask you all how is it done in SQL SERVER..please try to reply as soon as possible,
i will b waiting for the replies,
regards
View 3 Replies
View Related
May 8, 2006
Would like to know what naming convention you folks use.
What I need specifically is column naming conventions.
For instance, I have a table called 'lst_as400_srvr'. We could go with the vigorous:
CREATE TABLE lst_as400_srvr
(
as400_srvr_idintNOT NULLIDENTITY,
as400_srvr_namevarchar(128)NOT NULL,
as400_srvr_is_activebitNOT NULLDEFAULT 1
)
Or, I could loosen the rules a bit and go with:
CREATE TABLE lst_as400_srvr
(
idintNOT NULLIDENTITY,
namevarchar(128)NOT NULL,
is_activebitNOT NULLDEFAULT 1
)
I would lean towards the vigorous because it would be very obvious what data is being referenced by the name. In the loosened version, I could very easily have many tables with a 'id' column or a 'is_active' column.
Inversely, I would lean towards the loosened version because the names are a lot shorter and, thus, easier / faster to type.
I figure, if I'm going to learn a new standard, now's a good time to do so.
So, thoughts? Appreciate the help folks. :)
View 5 Replies
View Related
Feb 26, 2007
Sorry if this seems trivial, but is it allowed to add hyphens in a physical server name?
eg: PC-01
Will this cause any errors (when connecting, etc)
Thanx.
View 1 Replies
View Related
Dec 5, 2007
Is there any standard naming convention for SQL Server that microsoft suggest it?
the same as naming guidlines in MSDN for designing libararies in .Net Framework.
View 4 Replies
View Related
Dec 20, 2003
I have to build a table Physicalcharacterics in sql.
Convention for naming a table
Would I name this table in sql server
tbl_User_PhysicalCharacterics.
My main parent table is tbl_User.
Is that too long of a name.
View 2 Replies
View Related
Aug 21, 2006
how can i find the servername and sharefolder on my computer to access a file?
for the server name i used the ip aand sharefolder i used computer description.
i think its not right cause its not working.
View 18 Replies
View Related
Nov 6, 2015
How do you name sql linked-servers in your company ?
we have many legacy systems (random naming), but mostly newer systems follow this pattern
<ENVIRONMENT><APPLICATION><FUNCTION><VIRTUAL><NUMBER>
e.g.
PRD-FAX-DB-V01, TST-PAY-WEB-P02 etc.
We have link-servers all over the place. The ideal naming convention should satisfy following:
- developers should not have to modify code when deploying between environments DEV->TEST->PROD. This rules out using actual server name as linked-name.
- the name should easily identify actual server without much mental translation. This rules out relevant but generic names like FAXSERVER or PAYSERVER
- also, if the name could state that it is a linked-server e.g starting with LINK_, it works when reading the code.
So, I was thinking the link-name should simply remove the environment :
e.g LINK_FAX_DB_V01
that way, on DEV box, the underlying sql data source could point to the dev server, while on test, it could point to test server etc. without having to change code, and also knowing which server it's pointing to by just adding a TST or PRD in front-of it.
View 0 Replies
View Related
May 9, 2008
I did a search (google and on the forums) and found a few suggestions here and there, but I'd like something more complete to follow as far as naming conventions are concerned.
I wrote my first DB based on MySQL/Ruby/Active Record type naming convention...
- plural table names
- all lower cased
- underscores between words
- "id" is auto incrementer for each table
- something+"_at" is for datetime fields
- something+"_on" is for date fields
- referencing the primary id in another table is "tablename (singular)" + "_id".
This worked great in Ruby/MySQL, but in C#/SQL Server, its an ambiguity nightmare! All of my "id" fields conflict and alot of my tables have "added_at" datetime fields and they all conflict with each other. Essentially, any field that's named the same in one table as in another conflict on joins.
For example: users post comments to stories submitted by users...
table = articles
field 1 = id
field 2 = title
field 3 = body
field 4 = user_id
table = comments
field 1 = id
field 2 = title
field 3 = body
field 4 = user_id
field 5 = article_id
Trying to join these two tables is an ambiguity nightmare but I'd like to not have to name every field uniquely or start adding table prefixes to them all...
I guess I just need some good suggestions or links to recommended table structure/naming conventions for SQL Server. Thanks in advance!
View 3 Replies
View Related
Mar 14, 2008
I am currently trying to rename all of the Default constraints in my database to fit in with my naming convention of DF_+TableName+ColumnName. Since there are over 300 of the things, I don't particularly fancy doing them all manually.
I was hoping to use something like...
Code Snippet
--Declare the variables
DECLARE @defname VARCHAR(100), @tblname VARCHAR(100), @colname VARCHAR(100), @cmd VARCHAR(1000)
--Set the table name
SET @tblname = (SELECT object_name(parent_obj) FROM sysobjects
LEFT JOIN sys.columns ON sys.columns.object_id = sysobjects.id
INNER JOIN sysconstraints ON sysobjects.id = sysconstraints.constid
WHERE sysobjects.xtype = 'D')
--Set the column name
SET @colname = (SELECT name FROM sys.columns
INNER JOIN sys.tables ON sys.tables.object_id = sys.columns.object_id
WHERE sys.tables.name = @tblname)
--Set the default constraint
SET @defname = (SELECT name FROM sysobjects JOIN sysconstraints
ON sysobjects.id = sysconstraints.constid
WHERE object_name(sysobjects.parent_obj) = @tblname
AND sysobjects.xtype = 'D'
AND sysconstraints.colid = (SELECT colid FROM syscolumns
WHERE id = object_id(@tblname) AND
name = @colname))
--Set the renaming
SET @cmd = EXEC sp_rename +@defname+, 'DF_'+@tblname+'_'+@colname+, 'OBJECT'
--Execute the renaming
EXEC(@cmd)
I know that the above is not correct in any way, but it is how far I have got before I got really stuck! And I thought it might help you see what I was trying to get at...
Any clues?
View 5 Replies
View Related
Sep 2, 1998
I was wondering if there are generally accpeted naming standards for SQL Server ojbects (tables, store procedeures, triggers, views etc.) that might be available somewhere on the WEB. I was also wondering if most DBA`s prefix the object names like "sp_" or suffix the object like "Customer_T"? Any opinions?
View 1 Replies
View Related
Mar 6, 2014
I am trying to establish the standards for naming convention in my new project. What are the best standards which worked.
View 9 Replies
View Related
Jul 30, 2013
How i can find out the changes happened in database like modifying functions,table indexes,procedures and adding or removing columns..here in this query
select * from sys.objects
where type IS NOT NULL
and modify_date between '2013-07-21' and '2013-07-29'
but here i am getting created objects list and modifying list.but if i deleted any object it is not showing anything..how can i get the all the changes happened in database between specific dates.
View 3 Replies
View Related
Jun 5, 2007
I am trying to find any stored procedures or tables that have not been used in the last 2 months or so. Can anyone recommend me a good way to do this?
SQL 2005 Enterprise Edition SP 2
Thanks in advance.
Kay
View 1 Replies
View Related
Sep 28, 2006
Hi
I have a SQL Server 2005 database running. When I run some ddl changes, I want to find all the procs/objects that get invalidated because of object not found error.....
Is there any way that I can look up in sysdepends or other tables to find information about this.
Regards
Imtiaz
View 1 Replies
View Related
Apr 24, 2014
I need to find all the invalid objects means which will throw error on execution in a particular DB.
View 1 Replies
View Related
May 5, 2006
-- This stored procedure will let you search through your database
-- to find various objects that contain a particular string.
-- For example, you may want to see all tables and views that contain
-- a particular column.
use master
IF (object_id('sp_FindReferences') IS NOT NULL)
BEGIN
PRINT 'Dropping: sp_FindReferences'
DROP procedure sp_FindReferences
END
PRINT 'Creating: sp_FindReferences'
GO
CREATE PROCEDURE sp_FindReferences
(
@string varchar(1000) = '',
@ShowReferences char(1) = 'N'
)
AS
/****************************************************************************/
/* */
/* TITLE: sp_FindReferences */
/* */
/* DATE: 18 February, 2004 */
/* */
/* AUTHOR: WILLIAM MCEVOY */
/* */
/****************************************************************************/
/* */
/* DESCRIPTION: SEARCH SYSCOMMENTS FOR INPUT STRING, OUTPUT NAME OF OBJECT */
/* */
/****************************************************************************/
set nocount on
declare @errnum int ,
@errors char(1) ,
@rowcnt int ,
@output varchar(255)
select @errnum = 0 ,
@errors = 'N' ,
@rowcnt = 0 ,
@output = ''
/****************************************************************************/
/* INPUT DATA VALIDATION */
/****************************************************************************/
/****************************************************************************/
/* M A I N P R O C E S S I N G */
/****************************************************************************/
-- Create temp table to hold results
create table #Results
(
Name varchar(55),
Type varchar(12),
DateCreated datetime,
ProcLine varchar(4000)
)
IF (@ShowReferences = 'N')
BEGIN
insert into #Results
select distinct
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
''
from sysobjects SO
join syscomments SC on SC.id = SO.id
where SC.text like '%' + @string + '%'
union
select distinct
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
''
from sysobjects SO
where SO.name like '%' + @string + '%'
union
select distinct
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
''
from sysobjects SO
join syscolumns SC on SC.id = SO.ID
where SC.name like '%' + @string + '%'
order by 2,1
END
ELSE
BEGIN
insert into #Results
select
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
'Proc Line' = text
from sysobjects SO
join syscomments SC on SC.id = SO.id
where SC.text like '%' + @string + '%'
union
select
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
'Proc Line' = ''
from sysobjects SO
where SO.name like '%' + @string + '%'
union
select
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
'Proc Line' = ''
from sysobjects SO
join syscolumns SC on SC.id = SO.ID
where SC.name like '%' + @string + '%'
order by 2,1
END
IF (@ShowReferences = 'N')
BEGIN
select Name,
'Type' = Case (Type)
when 'P' then 'Procedure'
when 'TR' then 'Trigger'
when 'X' then 'Xtended Proc'
when 'U' then 'Table'
when 'C' then 'Check Constraint'
when 'D' then 'Default'
when 'F' then 'Foreign Key'
when 'K' then 'Primary Key'
when 'V' then 'View'
else Type
end,
DateCreated
from #Results
order by 2,1
END
ELSE
BEGIN
select Name,
'Type' = Case (Type)
when 'P' then 'Procedure'
when 'TR' then 'Trigger'
when 'X' then 'Xtended Proc'
when 'U' then 'Table'
when 'C' then 'Check Constraint'
when 'D' then 'Default'
when 'F' then 'Foreign Key'
when 'K' then 'Primary Key'
when 'V' then 'View'
else Type
end,
DateCreated,
ProcLine
from #Results
order by 2,1
END
drop table #Results
GO
IF (object_id('sp_FindReferences') IS NOT NULL)
PRINT 'Procedure created.'
ELSE
PRINT 'Procedure NOT created.'
GO
View 4 Replies
View Related
Nov 8, 2007
I'm trying to identify the objects in master that the role public has select permissions on, but when I run this query, I get 4 results where the default schema is null and the major_id column does not correspond to any records in the sys.all_objects table. Where else can I look to find what objects these are. DBO is listed as the grantor.
I appreciate your help.
SELECT *
FROM SYS.DATABASE_PERMISSIONS P,
SYS.DATABASE_PRINCIPALS R
WHERE P.GRANTEE_PRINCIPAL_ID=R.PRINCIPAL_ID and
permission_name='SELECT' and class_desc='OBJECT_OR_COLUMN' and
r.name='public'
order by r.name desc
View 9 Replies
View Related
Jan 31, 2007
Hi Frens,
Could anybody tell me how can I find all the database objects that are used in /referenced by/called by/dependent on a given stored procedure? In other words, I am looking for something like a stored procedure or a function that takes as input the name of a stored procedure and outputs all the names of the tables, functions, procedures, cursors and etc. database objects that are used in that procedure. Could you please give me suggestions or possible answers for this?
Thanks a lot for your time.
Regards,
-Ram.
View 11 Replies
View Related
Jul 25, 2014
How to alter all objects in database i want to find if can any syntax errors in my database after restoring from sql 2008 to 2012. I Can create as test and drop them but trying to find a way to alter proc , views and functions..
View 4 Replies
View Related
Jun 22, 2015
I am using SQL 2012 and I am trying to find buffer cache usage by DB objects within a particular DB.
I am running the following query
select
name as DB,
objname as db_object_name,
COUNT(name) as cache_page_count,
COUNT('x')*8.0/1024 as size_mb
[Code] ....
Following are the results:-
DB db_object_name cache_page_countsize_mb
TEST_DBNULL 428 3.34375
TEST_DBsysobjvalues 369 2.882812
TEST_DBsyscolpars 44 0.34375
TEST_DBsysssislog 38 0.296875
....
.....
Question- Why am I getting 428 pages for which there is no corresponding DB object? Why are so many pages present in sys.dm_os_buffer_descriptors but are missing from sys.allocation_units.
View 0 Replies
View Related
Nov 1, 2007
Hi,
If I have 2 tables:
TableA
aID
aCount
TableB
bID
bCount
And I need to create a 3rd table that will look like:
aID
bID
What should I call this table?
I've seen:
relAB
AXB
Any other naming conventions?
View 7 Replies
View Related
Oct 23, 2007
I have 1000's of tables. Some are of the form dbo.VT_2006-10-12. I'd like to drop all tables with the "VT" in the table name. How is the best done?
View 6 Replies
View Related
Dec 27, 2006
please help newbieI need to create a lot of objects the same type (let's say: schemas)I wish to use paramerized block in loop to do so.- how to put names of my objects to such control-flow?belss you for help
View 5 Replies
View Related
May 6, 2008
The Web Service Task seems to support calling methods using parameters but not (as far as I can see) using the Document/Literal calling convention. Is this correct? Is this likely to change in the future?
Thanks
*** Campbell
View 5 Replies
View Related
Nov 20, 2013
passing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX)
DECLARE @Handle AS INT
DECLARE @TransactionCount AS INT
SELECT @Config = '
<ConfigurationDirectory >
<ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"
[code]....
View 1 Replies
View Related
Sep 7, 2007
Just thought I'd point it out as something that needs fixing. This is inconsistent with every other MS product using the VS.net IDE framework.
I'd post it on Connect but then I'd wait 2 months to get "won't fix - this does not 'fit' with the current Katmai schedule" i.e. if I'm lucky (based on the new improved 3yr delivery of sql) it might get delivered in 2011. Wow.
Imagine if Xbox or PS3 designers told their customers, "sorry we know that up/down/left/right are in fact right/left/down/up, as you may expect on a game controller, but if you wait 3 years we might fix it"
View 3 Replies
View Related
Sep 28, 2007
Hello, I have 2 tables: Articles and Users. These 2 tables are related by AuthorId (FK) in Articles and UserId (PK) in Users. My question is: should the use the same name for the 2 keys, i.e., UserId? Or it is normal to use AuthorId in Articles table and UserId in Users table. This makes more sense. Just a naming question. Thanks, Miguel
View 5 Replies
View Related
Jun 18, 2006
What does everyone think of this method?I have a ton of tables like User, Project etc. I use the SAME column names for each table. For an example, ID, Name, Status etc instead of UserID etc.Only for relationship naming will I use UserID.The reason I do this is from a OOP perspective.My dad often said that a table was a entitiy of an object and each record in the table was a instance of that object.
View 2 Replies
View Related
May 3, 2005
I have a function which is named like this:
CREATE FUNCTION [GEEKASPNET.Split]
How can I use it in my stored procedure:
UPDATE Tasks SET Status = 0
WHERE TaskID IN (SELECT CONVERT(int, Value) FROM [GEEKASPNET].Split(@Tasks,","))
This does not work and gives me that "unreconized function GeekASPNET.Split
View 5 Replies
View Related
Jan 2, 2001
I am using SQL Server 7 w/ SP2. This may seem silly, but I'm trying to re-name a DTS local package -- so far without success. Surely there's a way to do this. Also, where is DTS info stored? That is, how does SQL Server store package names and other details? Thanks!
View 1 Replies
View Related
Oct 12, 2000
Does anyone know if there is someway to rename a DTS package? Or can I go into the msdb database
and delete the old name?
Thanks for your help.
Dianne
View 3 Replies
View Related
Jan 31, 2005
Most of the programming I do is in Access. I like to use naming conventions for all my tables, queries, etc. I am now moving several databases to SQL Server. Does anyone know of a good resource for naming convetions in SQL Server. Website, book?
View 2 Replies
View Related
