Geeks With Blogs

News


Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done SQL Server 2000 Tricks
Last n Months Display-Value Pair for DropDown List greater than a minimumm date
Create PROCEDURE [dbo].[ts_Dates] @isTo bit AS /* ---------------------------... --Last Dates Display-Value Pair for DropDown List for date greater than ---------------------------... exec [dbo].[ts_Dates] 0 exec [dbo].[ts_Dates] 1 */ declare @minToDate datetime declare @minFromDate datetime select @minToDate = min(<date>) from <table>; select @minFromDate = DATEADD(MONTH,-1,min(<da... from <table>; --print @minToDate --print ......

Posted On Tuesday, January 31, 2012 6:43 AM

Generate insert SQL for a table
Thanks to AndraaxAged Yak Warrior http://www.sqlteam.com/foru... Generate insert SQL for a table - Insert this stored proc: create proc [dbo].[generate_inserts] @table varchar(50) --Generate inserts for table @table AS declare @cols varchar(1000) declare @col varchar(50) /* generate_inserts 'PlanLevel1ControlResponsib... */ set @cols='' declare colcur cursor for select column_name from information_schema.columns where table_name=@table open colcur fetch next from colcur ......

Posted On Thursday, January 12, 2012 9:17 AM

list schema, table, row count in a database
---------------------------... --all info from Tables ---------------------------... SELECT * FROM sys.Tables ---------------------------... --Schema.name from Tables ---------------------------... SELECT '['+SCHEMA_NAME(schema_id)+... AS SchemaTable FROM sys.tables ---------------------------... --Schema, name from Tables ---------------------------... ......

Posted On Sunday, January 8, 2012 7:40 PM

Display Foreign Key Relationships and Name of the Constraint for Each Table in Database
Generate create script for all Foreign Keys Original article: http://connectsql.blogspot.... SELECT 'ALTER TABLE '+OBJECT_NAME(F.PARENT_OBJE... ' ADD CONSTRAINT' + F.NAME + ' FOREIGN KEY'+'('+COL_NAME(FC.PARENT... ')'+'REFRENCES '+OBJECT_NAME (F.REFERENCED_OBJECT_ID)+'(' +COL_NAME(FC.REFERENCED_OBJ... FROM SYS.FOREIGN_KEYS AS F INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC ON F.OBJECT_ID = FC.CONSTRAINT_OBJECT_IDGet ......

Posted On Thursday, December 15, 2011 7:04 AM

SQL Server Interview Questions
User-Defined Functions Scalar User-Defined Function A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. Table-Value User-Defined Function An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL ......

Posted On Saturday, February 26, 2011 2:43 PM

Select comma separated result from via comma separated parameter
Select comma separated result from via comma separated parameter PROCEDURE [dbo].[GetCommaSepStringsBy... (@CommaSepNumericIds varchar(max)) AS BEGIN /* exec GetCommaSepStringsByCommaSe... '1xx1, 1xx2, 1xx3' */ DECLARE @returnCommaSepIds varchar(max); with cte as ( select distinct Left(qc.myString, 1) + '-' + substring(qc.myString, 2, 9) + '-' + substring(qc.myString, 11, 7) as myString from q_CoaRequestCompound qc JOIN dbo.SplitStringToNumberTabl... AS ......

Posted On Monday, February 14, 2011 11:34 AM

Better than dynamic SQL - How to pass a list of comma separated IDs into a stored proc
Better than dynamic SQL - How to pass a list of comma separated IDs into a stored proc: Derived form "Method 6" from a great article: · How to pass a list of values or array to SQL Server stored procedure · http://vyaskn.tripod.com/pa... Create PROCEDURE [dbo].[GetMyTable_ListByCom... (@CommaSepReqIds varchar(500)) AS BEGIN select * from MyTable q JOIN dbo.SplitStringToNumberTabl... AS s ON q.MyTableId = s.ID End ALTER FUNCTION [dbo].[SplitStringToNumberT... ......

Posted On Friday, May 28, 2010 1:56 PM

List All Primary Keys and Foreign Keys
List All Primary Keys and Foreign Keys Original article: http://www.sqlteam.com/foru... set nocount on create table #PK(constraint_schema sysname not null, constraint_name sysname not null, sql varchar(4000) not null, constraint PK_#PK primary key clustered(constraint_schema, constraint_name)) create table #cols(constraint_schema sysname not null, constraint_name sysname not null, column_name sysname not null, ordinal_position int not null, constraint PK_#PKcol primary key ......

Posted On Thursday, October 23, 2008 3:49 AM

find columns and tables in a view
find columns and tables in a view SELECT vObj.name AS vName, vObj.id AS vID, vObj.xtype AS vType, dep.depid, dep.depnumber, tObj.name AS tName, col.colid, col.name AS cName FROM sysobjects vObj LEFT OUTER JOIN sysdepends dep ON vObj.id = dep.id LEFT OUTER JOIN sysobjects tObj ON dep.depid = tObj.id LEFT OUTER JOIN syscolumns col ON dep.depnumber = col.colid AND tObj.id = col.id WHERE vObj.xtype = 'V' And vObj.category = 0 and vObj.name = 'vw1_inv_item_det' ORDER BY vObj.name, tObj.name, col.name ......

Posted On Tuesday, April 29, 2008 4:54 AM

SQL Server format dates and times
original article: http://sqlserver2000.databa... Many people have asked if there is a way to make SQL Server behave the way FORMAT works in VB (and FormatDateTime in VBScript). What they'd like to see is the ability to tell SQL Server to format a date with long date and time, or in MM/DD/YYYY format, instead of having to memorize existing format conversion numbers and/or manipulate the strings themselves. For example, to get today's ......

Posted On Saturday, April 26, 2008 4:59 AM

create and myTable_Audit via for myTable, where myTable autoIdentity column name is myTable_ID
create and myTable_Audit via for myTable, where myTable autoIdentity column name is myTable_ID Steps: 1) Paste script below into Query Analyzer 2) set Query Analyzer results to "text" (not "grid" 3) set @v_Tablename = 'myTableName' below 4) set @v_postfix = '_ID' below (not 'myTableName_ID' ) 5) run script with no errors (script produces new script) 6) paste text results (new script) into query Query Analyzer 7) run script with no errors new table and triggers created Set NoCount On Go declare @v_Tablename ......

Posted On Thursday, March 15, 2007 10:47 AM

how to copy text data from one column to another
how to copy text data from one column to another EXEC sp_dboption 'database name', 'select into/bulkcopy', 'true' GO DECLARE @textData varchar(8000) select @textData = textData from table where id = 5 print '@textData' print @textData DECLARE @ptrvalDest binary(16) SELECT @ptrvalDest = TEXTPTR(xsl) FROM table WHERE id = 6 WRITETEXT table.textData @ptrvalDest @textData GO EXEC sp_dboption 'database name', 'select into/bulkcopy', 'false' GO ......

Posted On Wednesday, February 21, 2007 1:51 PM

@userDate match up with @FrequencyCode & @FrequencyValue
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER FUNCTION [Rodney Vinyard].[function_TodayIsR... -- ===========================... -- Author: <Rodney Vinyard> -- Create date: <1/31/07> -- Description: -- Does @userDate match up with @FrequencyCode & @FrequencyValue? -- -- <for input (1) @userDate (cannot use getDate() here in Function -- (2) @FrequencyCode -- (3) @FrequencyValue, returns 1 - true or 0 - false> -- ===========================... ......

Posted On Wednesday, January 31, 2007 10:27 AM

SQL Server 2000 Performance Tuning Tools
SQL Server 2000 Performance Tuning Tools original article: http://www.sqlteam.com/item... This article comes to us from Brad McGehee at www.sql-server-performance.... SQL Server 2000 includes several tools you may find useful when performance tuning your SQL Server applications. Query Analyzer Profiler Index Wizard Performance Monitor SQL Server 2000 Query Analyzer for developing and debugging Transact-SQL code for performance tuning Transact-SQL code. Show Execution Plan Whenever you ......

Posted On Monday, August 7, 2006 5:34 PM

Find any datasbase Column by partial Name in any User Table
SQL Server: SELECT sysobjects.name as "Table", syscolumns.name as "Column" from sysobjects , syscolumns where sysobjects.id = syscolumns.id and sysobjects.xtype = 'u' and syscolumns.name like '%YourFieldNameGoesHere%' order by sysobjects.name, syscolumns.name ---------------------------... --Get Table names and Row Counts ---------------------------... SELECT [TableAndCount] = so.name +' - '+CAST(MAX(si.rows)asvarcha... Rows' , [Name] = so.name FROM sysobjects so, sysindexes ......

Posted On Saturday, July 15, 2006 2:47 PM

Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net