their is a rule which i got to know before few days and i want to share with u that you cant use order by in functions....when u try to do so u will get this error..sg 1033, Level 15, State 1, Procedure GetDataBaseDetail, Line 21The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.so to solve this problem you will have to use...
Monday, March 30, 2009
Search metter in complete database
you can use this procedure to search a value in complete database ..SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- this procedure is created to search any value in all table of currently opened databaseALTER PROC [dbo].[SearchAllTables]( @SearchStr nvarchar(100))ASBEGIN CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2...
search name of table by column name
some times this problem occurs when ur database is too large and u know the name of column but u dont know the name of table it belongs from..so to search the name of table which contain that column, u can use this procedure.....SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate procedure [dbo].[GetTableOfColumn]--this procedure is to search a table name of column in a database by column name@ColVar varchar(30) =''asbeginSet...
Auto Documentation of database
Now u need not to write documentation in notebooks or by typeing all the tables names and fields names and their details in ms word...just by this single functiion u can easly get complete documentation of your current opened database...SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate function [dbo].[GetDataBaseDetail]() returns tableasreturn( SELECT top 10000 c.TABLE_NAME 'Table Name',c.column_name 'Column Name',data_type...
Subscribe to:
Posts (Atom)