create function dbo.fnc_varchar _columns_alltri m
()
returns @vysledek table
( tabulka sysname null,
sloupec sysname null,
pocet_zaznamu int null,
datovy_typ varchar(256) null
)
as
begin
declare @sql_text nvarchar(2000),
@table_name varchar(256),
@column_name varchar(256),
@dat_typ varchar(256),
@celkem int,
@int_objSQL int,
@int_erros int,
@int_objSelectC ountResult int,
@bint_SelectCou nt bigint
declare @cislo table
( pocet int
)
declare @pom table
( table_name sysname,
column_name sysname,
datovy_typ varchar(256)
)
insert into @pom
select o.name as table_name, c.name as column_name, t.name as datovy_typ
from sys.objects o
left join sys.columns c on c.object_id = o.object_id
left join sys.types t on t.user_type_id = c.user_type_id
where o.type = 'U' and o.name not in ('cVykazPriloha ','cVysledovka' ,'cRozvaha','dt properties') and o.name not like 'rep%'
and c.user_type_id in (167,231) and c.name not in ('key')
and o.name = 'adm_DB_version ' and c.name = 'version'
order by o.name
while exists (select 1 from @pom)
begin
select top 1 @table_name=tab le_name,@column _name=column_na me,@dat_typ=dat ovy_typ from @pom
delete from @cislo
exec @int_erros = sp_OACreate 'SQLDMO.SQLServ er', @int_objSQL OUTPUT
exec @int_erros = sp_OASetPropert y @int_objSQL, 'LoginSecure', TRUE
exec @int_erros = sp_OAMethod @int_objSQL, 'Connect', null, '.'
set @sql_text = 'select count(*) from dbo.' + @table_name + ' where len(' + @column_name+ ') <> len(ltrim(rtrim (' + @column_name+ ')))'
set @sql_text = 'ExecuteWithRes ults("' + @sql_text + '")'
exec @int_erros = sp_OAMethod @int_objSQL, @sql_text, @int_objSelectC ountResult OUTPUT
exec @int_erros = sp_OAMethod @int_objSelectC ountResult, 'GetRangeString (1, 1)', @bint_SelectCou nt OUT
exec @int_erros = sp_OADestroy @int_objSQL
set @celkem = @bint_SelectCou nt
if @int_erros <> 0
exec sp_OAGetErrorIn fo @int_objSQL
if @celkem > 0
insert into @vysledek (tabulka,sloupe c,pocet_zaznamu ,datovy_typ) values (@table_name,@c olumn_name,@cel kem,@dat_typ)
delete top (1) from @pom
end
return
end
go
0 2012 Sign in to post your reply or Sign up for a free account.
Similar topics |
by: diroddi |
last post by:
I am having a problem using a dynamic where clause. I have a feeling
that I am overlooking something very simple, although I can't seem to
figure it out.
The error i'm getting is: You have an error in your SQL syntax. Check
the manual that corresponds to your MySQL server version for the right
syntax to use near 'ORDER BY full_name ASC LIMIT 0,25'
Any help would be greatly appreciated.
|
by: windandwaves |
last post by:
Hi Folk
I was wondering if it is possible to find out what people click on, using
PHP.
For example, I have a PHP page with an email link and some third party links
(e.g. www.someone-else.com). I want to know where people go.
My own solution would be to make the link not to an email or a third-party
website, but to another page along the lines of
|
by: Andrey Tatarinov |
last post by:
Hi.
It would be great to be able to reverse usage/definition parts in
haskell-way with "where" keyword. Since Python 3 would miss lambda, that
would be extremly useful for creating readable sources.
Usage could be something like:
>>> res = where:
>>> def f(x):
|
by: KathyB |
last post by:
Hi, I have 2 tables:
tblStations StationID
Station
tblStationUser RecordID
UserName
Station
I'm trying to come up with a dataset that contains the
|
by: John |
last post by:
Hi,
How do websites like google page through large numbers of records?
For example; if I do a search on Google, get 187,000 matches, and go to the
22nd page of the results; I'm sure Google doesn't do a new SELECT statement
and traverse through 440 records (22pgs x 20 per page). But I'm also pretty
sure Google doesn't keep a hell of a lot of session data open either.
I know there are all kinds of ways to do this, I've created hacks...
| |
by: Roy Gourgi |
last post by:
Hi,
I am trying to add a row to my table but I get the error message
"invalid column name SOBN and BN1" on this statement. Basically, I am trying
to add the row into the same table that I am searching if it does not find
SOBN = 5 and BN1 =3. What is the problem? Do I have to create a primary
key? Is it possible to have more than 1 primary key (i.e. secondary
....... ).
strCommand = "INSERT INTO tblSQL_2 (SOBN, BN1) SELECT SOBN , BN1...
|
by: Pavel Stehule |
last post by:
Hello,
Pg make query 1. and 2. very fast (use index), but for query 3. dont use
index. I can solve its using select union, but I readed so pg 7.5 don't
problem with OR operator. I use cvs pg. I used vacuum analyze first.
table sp_op_product has 15K rows, sp_op_uct 37K rows;
regards
Pavel Stehule
|
by: Silvio Matthes |
last post by:
Hello,
I'm new to the list and did not find a suitable answer to my question so
here it is:
I try to select the rows of a table where the content of a varchar-column
is empty ('') and PostgresQL is doing a seqscan.
I've tried this on a PostgresQL-Serverversion 7.3.4 and 8.0 beta1.
|
by: Phillip Vong |
last post by:
I'm a newbie using VS2005 to learn and test. I'm testing against the
Northwind DB.
I created a simple variable called "myVariable" and I assigned it the
character "c". I want my SQL query to use this variable in the "Where"
statement where it will pull up all names with the letter "c" in it. How do
I pass this variable on the the WHERE statement? I thought all I had to do
was use the <% %> with my variable inside and that would work...
|
by: Emin |
last post by:
Dear Experts,
I have a fairly simple query in which adding a where clause slows
things down by at least a factor of 100. The following is the slow
version of the query
-------------------------
SELECT * FROM
( Select x.event_date From x FULL OUTER JOIN y
ON x.event_date = y.event_date
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |